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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With