Excel VBA Userform - Sub run when something changes

Question

I have a custom form containing many text fields. When the values ​​of the values ​​of these text fields change, I need to recalculate the values ​​of the final results based on the values ​​of the text field by calling the AutoCalc () routine.

I have about 25 mailboxes, and I do not want to add the Change () event individually for each text field that invokes the specified routine. What is the fastest and most efficient way to call AutoCalc () when a value changes?

Answer 1

This can be achieved using the class module. In the following example, I assume that you already have a custom form with some text fields on it.

First, create a class module in a VBA project (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 user form add the following 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 

Answer 2

Using a class, as follows from the answer above, is a good strategy for solving many controls in a compressed and elegant way:

1) I do not see problems when creating 25 events with 1 line, calling the general private procedure privateform, unless the number of controls is dynamic. This is KISS philosophy.

2) In general, I consider the Change event to be very alarming, because it performs the entire recount of each digit. It is more reasonable and moderate to do this using the Exit or Before upgrade event because it only recounts when deciding on the value. For example, Google Instant annoys me, trying to return answers, consuming resources, without the user asking a question.

3) There was a verification problem. I agree that you can avoid the wrong keys with the Change event, however, if you need to verify the data, you cannot know whether the user will continue to enter or if the data is ready for verification.

4). You must remember that the Change or Exit events do not force the user to go through text fields, so the system must be redefined and recounted 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 

You depend on saving time, you can create a general VBA procedure to generate code for events related to controls in the form corresponding to the mask. This code may be in the draft sheet (it is safer to directly generate the code, which is a mistake in some versions of Excel), and not copy and paste into the 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 

Answer 3

Take a look at this one to create a class that responds to a change in any text field. An example for buttons, but can be changed. However, keep in mind that Textbox controls do not have an Exit event (this event is actually part of the user form), so you really need to use the Change event.

Answer 4

However, remember that Textbox controls do not have an Exit event (this event is actually part of the user form), so you really need to use the Change event.

I'm confused. Perhaps this was added in 2007, or maybe I do not understand the nuances. I am using the Exit event for TextBox controls. When I exit the control panel or click on another control, it fires the Exit event.

Answer 5

I had a similar problem when I want to check about 48 different text fields using the general procedure, and the module class approach looked interesting (much less duplicate lines of code). But I did not want to check for every character entered, I only wanted to check after the update. And if the entered data was invalid, I wanted to clear the text field and stay in the same text field, which requires the use of Cancel = True in the Exit routine. After hours of trying this and the lack of AfterUpdate and Exit event handlers, I never ran why I discovered why.

If you create a class as follows:

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

and then go to the VBE object browser and select MyTextBox, you will see that the listed events do not include AfterUpdate or Exit. These events are available if you go into UserForm and use the VBE object browser and watch the TextBox instance, but they seem to be inherited from the controls, of which the TextBox is a part. Defining a new class using MSForms.TextBox does not include these events. If you try to define these event handlers manually, they will compile and it seems they will work (but they do not). Instead of becoming event handlers for the class object, they will simply be private routines that are displayed in (General) in the VBE Object Browser and are never executed. It seems that the only way to create a valid event handler is to select a class object in the VBE object browser and then select the desired event from the list of enumerated events.

After many hours of searching, I could not find the links to show how a similar inheritance model could be built in a private class, so AfterUpdate and Exit will be displayed as available events for the created classes. Thus, the recommendation (above) about having a separate event handler for each TextBox in UserForm may be the only approach that will work if you want to use AfterUpdate and / or Exit.

Answer 6

So, the first 9 lines that are given to me on the forum, I can’t remember where. But I built on this, and now I would like to use the command button to recalculate if using modifies the variable specified in this section.

 <pre><code>'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'</pre></code>