Excel VBA Userform - Execute Sub when something changes

I have a userform containing lots of text boxes. When ever the values of these text boxes changes, I need to recalculate my end result values based on the textbox values by calling a subroutine AutoCalc().

I have around 25 boxes and I don't want to add a Change() event individually to each textbox calling the said subroutine. What's the quickest and efficient way to call the AutoCalc() whenever some value changes?

Solution 1:

This can be achieved by using a class module. In the example that follows I will assume that you already have a userform with some textboxes on it.

Firstly, create a class module in your VBA project (let call it clsTextBox -- be sure to change the 'Name' property of the class module!)

Private WithEvents MyTextBox As MSForms.TextBox

Public Property Set Control(tb As MSForms.TextBox)
    Set MyTextBox = tb
End Property

Private Sub MyTextBox_Change()
    AutoCalc() //call your AutoCalc sub / function whenever textbox changes
End Sub

Now, in the userform, add the folowing code:

Dim tbCollection As Collection

Private Sub UserForm_Initialize()
    Dim ctrl As MSForms.Control
    Dim obj As clsTextBox

    Set tbCollection = New Collection
        For Each ctrl In Me.Controls
            If TypeOf ctrl Is MSForms.TextBox Then
                Set obj = New clsTextBox
                Set obj.Control = ctrl
                tbCollection.Add obj
            End If
        Next ctrl
    Set obj = Nothing

End Sub

Solution 2:

The class use, as the answer above suggests, it is a good strategy to deal with many controls in a concise and elegant way, however:

1) I see no problems in creating 25 events with 1 line, calling a common userform private routine, unless the number of controls is dynamic. It's a KISS philosophy.

2) Generally, I consider the Change event very disturbing because he does all the recalculation each digit entered. It is more sensible and moderate do this using the Exit event or Before Update event, because it makes the recalculation only when deciding on a value. For instance, The Google Instant annoy me trying to return responses, consuming resources, without the user having defined the question.

3) There was a validation problem. I agree that you can avoid wrong keys with Change event, however if you need to validate the data, you can not know if the user will continue typing or if the data is ready to be validated.

4) You should remember that Change or Exit events does not force the user to pass in text fields, so the system needs to be revalidated and recalculated when trying to exit the form without canceling.

The following code is simple but effective for static forms.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call  AutoCalc(Cancel)
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call  AutoCalc(Cancel)
End Sub
.....
Private Sub TextBox25_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Call  AutoCalc(Cancel)
End Sub

Private Function Valid
.....
End Function 

Private Sub AutoCalc(Canc As Variant)
If Not Valid() Then Canc=True
'  Calculation
End Sub

It you are addicted to save time, you can create a generic VBA routine in order to generate code for events related to controls in a form that fit a mask. This code can be in a draft sheet (it's safer that generate directly code, that is buggy in some Excel versions) and than copy and paste to a form module.

 Sub GenerateEvent(Form As String, Mask As String, _
   Evento As String, Code As String)
 '  Form - Form name in active workbook
 '  Mark - String piece inside control name
 '  Evento - Event name to form procedure name
 '  Code   - Code line inside event
 Dim F As Object
 Dim I As Integer
 Dim L As Long
 Dim R As Range
 Dim Off As Long
 Set F = ThisWorkbook.VBProject.VBComponents(Form)
 Set R = ActiveCell   ' Destination code
 Off = 0
 For I = 0 To F.Designer.Controls.Count - 1
    If F.Designer.Controls(I).Name Like "*" & Mask & "*" Then
        R.Offset(Off, 0) = "Private Sub " & _
          F.Designer.Controls(I).Name & "_" & Evento & "()"
        R.Offset(Off + 1, 0) = "     " & Code
        R.Offset(Off + 2, 0) = "End Sub"
        Off = Off + 4
    End If
 Next I
 End Sub

 Sub Test()
 Call GenerateEvent("FServCons", "tDt", "Exit", _
    "Call AtuaCalc(Cancel)")
 End Sub

Solution 3:

Take a look at this for how to create a class that responds to a change in any textbox. The example is for buttons, but can be modified. However, be aware that Textbox controls don't have an Exit event (that event is actually part of the userform) so you really will have to use the Change event.

Solution 4:

I had a similar issue where I want to validate approximately 48 different textboxes using a common routine and the class module approach looked interesting (a lot fewer duplicated lines of code). But I didn't want to validate on every character entered, I only wanted to check after the update. And if the data entered was invalid I wanted to clear the textbox and stay in the same textbox which requires the use of Cancel = True in the Exit routine. After several hours of trying this and not having my AfterUpdate and Exit event handlers never trigger I discovered why.

If you create a class like the following:

Private WithEvents MyTextBox As MSForms.TextBox

Public Property Set** Control(tb As MSForms.TextBox)

    Set MyTextBox = tb

End Property

and then you go into the VBE object browser and select MyTextBox, you will see the enumerated events supported do not include AfterUpdate or Exit. These events are available if you go into the UserForm and use the VBE object browser and look at an instance of a TextBox, but they appear to be inherited from the Controls that the TextBox is a part of. Defining a new class using MSForms.TextBox does not include those events. If you attempt to define those event handlers manually, they will compile and it appears they would work (but they don't). Instead of becoming event handlers of the class object, they will just be private sub routines that show up in (General) under the VBE object browser and never get executed. It appears the only way to create a valid event handler is to select the class object in the VBE object browser and then select the desired event from the enumerated events list.

After many hours of searching I've been unable to find any references to show how a similar inheritance model can be constructed within a private class so AfterUpdate and Exit would show up as available events for the created classs. So the recommendation (above) of having a separate event handler for each TextBox on a UserForm, may be the only approach that will work if you want to use AfterUpdate and/or Exit.