How to capture event of multiple Dynamic control in VBA

Question

From a previous post I learned a way to populate a userform with a grid of textboxes:

Dim Grid(1 To 10, 1 To 5) As MSForms.TextBox

Private Sub UserForm_Initialize()

Dim x As Long
Dim y As Long

For x = 1 To 10
    For y = 1 To 5
        Set Grid(x, y) = Me.Controls.Add("Forms.Textbox.1")
        With Grid(x, y)
            .Name = "TextBox_" & x & "_" & y
            .Width = 50
            .Height = 20
            .Left = y * .Width
            .Top = x * .Height
            .SpecialEffect = fmSpecialEffectFlat
            .BorderStyle = fmBorderStyleSingle
        End With
    Next y
Next x

End Sub

Now, I need to run certain code when I change the contents of any textbox in columns 5 and 6. But since the textbox won't exist until after Initialize is run, their Change events don't exist either.

So I need to either:

• Write the change events in advance, since I know the names of the textboxes in advance.
• Use an event that will trigger whenever I click any textbox, and be able to identify the textbox in question.

If the only way to do this is by using a class module, please explain it carefully, since I've never actually used one.

EDIT: The answers from @TinMan and @Storax work a little too well. The code reacts to every keystroke in the textbox, but I really need to wait until the user is finished typing. There's no "Exit" event for the textbox when it's in the class module. Any thoughts?

Answer 1

You'll need to create a class to listen for the changes.

Class: TextBoxListener

Public WithEvents TextBox As MSForms.TextBox
Public UserForm As Object

Private Sub TextBox_Change()
    UserForm.TextBoxGridChange TextBox
End Sub

Userform

With a few modifications you can use the Grid() to hold the TextBoxListeners references.

Option Explicit
Private Grid(1 To 10, 1 To 5) As New TextBoxListener

Public Sub TextBoxGridChange(TextBox As MSForms.TextBox)
    Debug.Print TextBox.Value
End Sub

Private Sub UserForm_Initialize()
    
    Dim x As Long
    Dim y As Long

    For x = 1 To 10
        For y = 1 To 5
            With Grid(x, y)
                Set .TextBox = Me.Controls.Add("Forms.Textbox.1")
                Set .UserForm = Me
                With .TextBox
                    .Name = "TextBox_" & x & "_" & y
                    .Width = 50
                    .Height = 20
                    .Left = y * .Width
                    .Top = x * .Height
                    .SpecialEffect = fmSpecialEffectFlat
                    .BorderStyle = fmBorderStyleSingle
                End With
            End With
        Next y
    Next x
    
End Sub

Answer 2

Just a simple example how the class could look like for the textboxes. I named the class clsTextBoxes

Option Explicit

Public WithEvents tb As MSForms.TextBox
' just to keep track of the box in the grid
Public x As Long
Public y As Long

' Just a simple example for the change event.
' you could  use x and y to tell the different textboxes apart
Private Sub tb_Change()
    Debug.Print tb.Text, x, y
End Sub

You have to adjust your code in the userform like that

Option Explicit

Dim Grid(1 To 10, 1 To 5) As MSForms.TextBox

' Collection to save all the textboxes in the grid
Dim colTxt As New Collection

Private Sub UserForm_Initialize()

Dim x As Long
Dim y As Long
Dim cTxt As clsTextBoxes

For x = 1 To 10
    For y = 1 To 5
        Set Grid(x, y) = Me.Controls.Add("Forms.Textbox.1")
        
        ' create an new clsTextBoxes
        Set cTxt = New clsTextBoxes
        ' save a pointer to the just created textbox
        Set cTxt.tb = Grid(x, y)
        ' store the postion
        cTxt.x = x
        cTxt.y = y
        ' add it to the collection
        colTxt.Add cTxt
        
        With Grid(x, y)
            .Name = "TextBox_" & x & "_" & y
            .Width = 50
            .Height = 20
            .Left = y * .Width
            .Top = x * .Height
            .SpecialEffect = fmSpecialEffectFlat
            .BorderStyle = fmBorderStyleSingle
        End With
    Next y
Next x

End Sub

Look at the comments for a short explanation