How to create _Change() Event for dynamically created TextBox in VBA UserForm?

Question

I am trying to add _Change() event to dynamically created TextBox using classes in VBA. However there is nothing happening, when I try to run my code. Could you please point me where I am wrong?

I have got class conditionEventClass

Public WithEvents conditionEvent As MSForms.textBox

Public Property Let textBox(boxValue As MSForms.textBox)
    Set conditionEvent = boxValue
End Property

Public Sub conditionEvent_Change()
    MsgBox conditionEvent.Name & " changed."
End Sub

I have got following code in my module:

Sub addConditions()
    Dim conditionCommand As conditionEventClass
    Dim newTextBox As MSForms.textBox
    

        
    Set newTextBox = commandRequestForm.MultiPage1(1).Controls.Add("Forms.TextBox.1", "conditionValue", True)
    With newTextBox
         .Name = "conditionValue"
         .Left = 750
         .height = 15
         .Width = 100
         .Top = 20 
    End With
    
    Set conditionCommand = New conditionEventClass
    conditionCommand.textBox = newTextBox
    
End Sub

I expect that my sub conditionEvent_Change() is going to show msgBox. But unfortunately nothing happens.

Answer 1

Talking about only a single Text Box, you can use the next simpler way:

1.Declare a private variable on top of the form code module (in the declarations area):

    Private WithEvents myTextBox As MSForms.TextBox

2. Then, create the event for the above declared variable:

Private Sub myTextBox_Change()
   MsgBox activecontrol.name & " changed."
End Sub

3. Use your adapted code as:

Sub addConditions()
    Dim newTextBox As MSForms.TextBox

    Set newTextBox = commandRequestForm.MultiPage1(1).Controls.Add("Forms.TextBox.1", "myTextBox", True)
    With newTextBox
         .left = 10
         .height = 15
         .width = 100
         .top = 20
    End With
    
    Set myTextBox = newTextBox
End Sub

For 1 to 3, 4 such controls you can use the simpler (above shown) way. If you need creating on the fly a lot of such controls, I can show you how to adapt your code...

Edited:

Please, use the next working way using a class to be assigned to many text boxes created on the fly:

1. Copy the next code in a class module and name it 'clsTBox':

Option Explicit

Public WithEvents newTBox As MSForms.TextBox

Private Sub newTBox_Change()
   MsgBox newTBox.name & " changed."
End Sub

2.Declare a Private variable on top of the form code module:

  Private TBox() As New clsTBox

3. Use the next Sub to create three text boxes and assign the Click event to them:

Private Sub CreateThreeTB() 
    Dim i As Long, txtBox01 As MSForms.TextBox, leftX As Double, tWidth As Double, k As Long
    
    leftX = 20: tWidth = 50
    ReDim TBox(100) 'use here the maximum number of text boxes you intend creating
    For i = 1 To 3
         Set txtBox01 = Me.Controls.Add("Forms.TextBox.1", "dynTxtBox_" & i)
        With txtBox01
            .top = 10
            .left = leftX: leftX = leftX + tWidth
            .width = tWidth
            .Text = "something" & i
        End With
        
        Set TBox(k).newTBox = txtBox01: k = k + 1
    Next i
    ReDim Preserve TBox(k - 1)
End Sub

4. Call the above Sub from Initialize event or from another control, play with the newly created text boxes value and see how the change event is triggered...