0

I have a userform in the VBE with 12 textboxes (1 for each month of the year). There's an additional textbox that is supposed to display the sum of the values in each of the first 12 boxes, updating and recalculating itself as the values in the other textboxes change.

Some help in implementing a solution to this problem is much appreciated.

The code I'm trying to implement is taken from the post at:

VBA: Detect changes in any textbox of the userform

However I'm unsure what to put in the event section below of the class.

NB: This is my first attempt at using classes so I'm just learning.

Community
  • 1
  • 1
Albert
  • 61
  • 8
  • with what ? haven't provided any helpful information ? upload your latest code attempt , your user-from, something that we can help you – Shai Rado Sep 04 '16 at 08:10
  • Thanks Shai Rado. I've edited the post and added a bit more information. Hopefully you can assist given this additional info. – Albert Sep 06 '16 at 07:11

1 Answers1

1

If Class1 is the name of your class module, Userform1 the name of your userform and if you want the sum of all textbox values to be in TextBox13 then,

in your Class1 module insert

Private WithEvents txtbox As MSForms.TextBox
Dim ctlr As Control
Public sum As Integer

Public Property Set TextBox(ByVal t As MSForms.TextBox)
    Set txtbox = t
End Property

Private Sub txtbox_Change()
    sum = 0
    For Each ctlr In UserForm1.Controls
        sum = sum + Val(ctlr)
    Next ctlr
    UserForm1.TextBox13 = sum - Val(UserForm1.TextBox13)
End Sub

and in the UserForm1 module insert

Private myEventHandlers As Collection

Private Sub UserForm_Initialize()
    Dim txtbox As Class1

    Set myEventHandlers = New Collection

    Dim c As Control
    For Each c In Me.Controls
        If TypeName(c) = "TextBox" Then
            Set txtbox = New Class1

            Set txtbox.TextBox = c

            myEventHandlers.Add txtbox
        End If
    Next c
End Sub
Arun Thomas
  • 805
  • 1
  • 12
  • 21
  • Thanks @ArunThomas . I'm looking to have one event procedure detecting changes in any of the text boxes rather than separate ones as your post suggests. I've edited the post and added some relevant information. – Albert Sep 06 '16 at 07:15
  • see my updated answer, code tested and working for me – Arun Thomas Sep 06 '16 at 19:40
  • Perfect solution @ArunThomas. Much appreciated. – Albert Sep 07 '16 at 07:00