Open the VBEditor and insert a class module. Use the Properties window to change its name to clsAnyTextBox
Then put this code in that class module.
' in clsAnyTextBox module
Public WithEvents TextBox As MSForms.TextBox
Event Change()
Private Sub TextBox_Change()
Set TextBox.Parent.anyTextBox = Me
RaiseEvent Change
End Sub
Then you can put code like this in your userform's code module
' in userform code module
Public WithEvents anyTextBox As clsAnyTextBox
Dim myTextBoxes As Collection
Private Sub UserForm_Initialize()
Dim oneTextbox As Variant
Dim oneAny As clsAnyTextBox
Dim I As Long
Set myTextBoxes = New Collection
Rem matches each textbox of interest to its linked cell
For I = 0 to 2
Array(Me.TextBox1, Me.TextBox2, Me.TextBox3)(I) = Array(Range("A1"),Range("B2"),Range("C3"))(I).Address(,,,true)
Next i
Rem for each textbox of interest
' creates a clsAnyTextBox object,
' assigns that text box to that custom object,
' and puts the custom object in a collection
For Each oneTextbox In Array(Me.TextBox1, Me.TextBox2, Me.TextBox3)
Set oneAny = New clsAnyTextBox
Set oneAny.TextBox = oneTextbox
myTextBoxes.Add Item:=oneAny, Key:=oneTextbox.Name
Next oneTextbox
Set oneAny = Nothing
End Sub
Private Sub anyTextBox_Change()
With anyTextBox.TextBox
msgBox .Name & " has changed."
Range(.Tag) = .Text
End With
End Sub
Your loop through textboxes (in the Initialize event) might be different.
Note that the userform variable anyTextBox is a clsAnyTextBox object. To refer to the textbox itself, use anyTextBox.TextBox
Bookmarks