There is a userform that has many textboxes and I need to detect changes in each. So I have write a subroutine for every textbox in the form and it turns out a large piece of code. As the code for every textbox is the same I want to optimize it. So is it possible to write just one subroutine that detect changes in any textbox of the form?
The only way do achieve that is to use a class along with WithEvents
Here's a minimal example:
Code for the class module named mytextbox
:
Private WithEvents txtbox As MSForms.TextBox
Public Property Set TextBox(ByVal t As MSForms.TextBox)
Set txtbox = t
End Property
Private Sub txtbox_Change()
' code for handling the event
End Sub
And the code inside the Userform, assuming you want to handle the events of every Textbox
Private myEventHandlers As Collection
Private Sub UserForm_Initialize()
Dim txtbox As mytextbox
Set myEventHandlers = New Collection
Dim c As Control
For Each c In Me.Controls
If TypeName(c) = "TextBox" Then
Set txtbox = New mytextbox
Set txtbox.TextBox = c
myEventHandlers.Add txtbox
End If
Next c
End Sub