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?

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

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

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.

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.

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.

I'm confused. Perhaps this was added in 2007, or perhaps I don't understand the nuances. I use the Exit event on TextBox controls. When I Tab out of the control, or click the mouse on another control, it triggers the Exit event.

Two Class Module Method

I've created a very easy way to add event listeners to a userform. Additionally, it adds events such as MouseOver and MouseOut. (Cool for doing hover effects)

The two class modules that need to be imported in order to work can be found on my Github page VBA Userform Event Listeners

How to use the code in a Userform

It's easy to get started, once you add my class modules, simple add the sample code below to a Userform.

Private WithEvents Emitter As EventListnerEmitter
    
Private Sub UserForm_Activate()
   Set Emitter = New EventListnerEmitter
   Emitter.AddEventListnerAll Me
End Sub

That's it! Now you can start listening for different events.

The Main Event Listener

There is the main event EmittedEvent. This passes in the control that the event on, and the event name. So all events go through this event handler.

Private Sub Emitter_EmittedEvent(Control As Object, ByVal EventName As String, EventValue As Variant)
    
    If TypeName(Control) = "Textbox" And EventName = "Change" Then
        'DO WHATEVER
    End If
  
End Sub

Individual Event Listeners

You can also just listen for the specific events. o in this case the change event.

Private Sub Emitter_Change(Control As Object)

    If TypeName(Control) = "Textbox" Then
          'DO WHATEVER
    End If
    
End Sub

Please feel free to check out my Github page and make a pull request as not all the events are being captured yet.

So the first 9 lines where given to me in a forum I can't remember where. But I built on that and now I would like to use a command button to re-calculate if the use changes a variable listed in this sub.

Private Sub txtWorked_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    11 Dim OTRate       As Double
       OTRate = Me.txtHourlyRate * 1.5
    If Me.txtWorked > 40 Then
       Me.txtBasePay.Value = Format(Me.txtHourlyRate.Value * 40, "$#,##0.00")
       Me.txtOvertime = Format((Me.txtWorked - 40) * OTRate, "$#,##0.00")
    Else
       Me.txtOvertime.Value = "0"
       Me.txtBasePay.Value = Format(Me.txtHourlyRate.Value * Me.txtWorked.Value, "$#,##0.00")
    End If
    Dim Gross, W2, MASSTax, FICA, Medi, Total, Depends, Feds As Double
       Gross = CDbl(txtBonus.Value) + CDbl(txtBasePay.Value) +    CDbl(txtOvertime.Value)
       W2 = txtClaim * 19
       Me.txtGrossPay.Value = Format(Gross, "$#,##0.00")
       FICA = Gross * 0.062
       Me.txtFICA.Value = Format(FICA, "$#,##0.00")
       Medi = Gross * 0.0145
       Me.txtMedicare.Value = Format(Medi, "$#,##0.00")
       MASSTax = (Gross - (FICA + Medi) - (W2 + 66)) * 0.0545
    If chkMassTax = True Then
       Me.txtMATax.Value = Format(MASSTax, "$#,##0.00")
    Else: Me.txtMATax.Value = "0.00"
    End If
    If Me.txtClaim.Value = 1 Then
       Depends = 76.8

    ElseIf Me.txtClaim.Value = 2 Then
       Depends = 153.8

    ElseIf Me.txtClaim.Value = 3 Then
       Depends = 230.7
    Else
       Depends = 0
    End If
       If (Gross - Depends) < 765 Then
       Feds = ((((Gross - Depends) - 222) * 0.15) + 17.8)
       Me.txtFedIncome.Value = Format(Feds, "$#,##.00")
    ElseIf (Gross - Depends) > 764 Then
       Feds = ((((Gross - Depends) - 764) * 0.25) + 99.1)
       Me.txtFedIncome.Value = Format(Feds, "$#,##.00")
    Else:
       Feds = 0
    End If
       Total = (txtMATax) + (FICA) + (Medi) + (txtAdditional) + (Feds)
       Me.txtTotal.Value = Format(Total, "$#,##0.00")
       Me.txtNetPay.Value = Format(Gross - Total, "$#,##0.00")

End Sub

Private Sub cmdReCalculate_Click()

End Sub