Creating Change Events on Dynamically Created Textboxes

[Qwest336]

Hello all,

I hope this finds you all healthy and safe!

I have a Userform where I have populated some textboxes in Design and others dynamically. For the textboxes that I have created dynamically, I need to populate the Change event with code to run a routine in a module.

I'm creating the Textboxes in a Sub called CreateControl like this:

Public newTxtAssigned As MSForms.TextBox


Sub CreateControl()
    Dim newLblMain, newLblMinutes As MSForms.Label
    Dim newTxtMPA, newTxtMax, newTextA2A As MSForms.Control
    Dim i As Integer, TopAmt
    Dim sht As Worksheet
    Dim LastRow As Long
    Dim NumberOfLabels As Long
    Dim StartingRow As Long
    Dim MaxControls As Long


Dim objMyEventClass As New Class1

Set sht = ThisWorkbook.Worksheets("Pivot")

    LastRow = sht.Range("ActionList").Rows.Count + 2 'Adding two rows to account for the filters in the pivot
    NumberOfLabels = LastRow - 3
    StartingRow = 4
'Number of controls on the userform
    MaxControls = 16


Call mdlControlActions.DeleteControls

'Sets the Top location for the first control after the 4 static controls.  All others will be calculated from this one.
    TopAmt = 242

    For i = 0 To NumberOfLabels - 1
        Set newLblMain = usrProductionReporting.Controls.Add("Forms.Label.1")
        With newLblMain
            .Name = i & "lblMain" & sht.Range("A" & StartingRow + i).Value
            .Top = TopAmt + 6
            .Visible = True
            .Caption = sht.Range("A" & StartingRow + i).Value & ":"
            .Tag = "LabelMain"
        End With

        Set newTxtAssigned = usrProductionReporting.Controls.Add(bstrProgID:="Forms.Textbox.1", Name:="Textbox" & i)
        With newTxtAssigned
            .Name = i & "txtAssigned" & sht.Range("A" & StartingRow + i).Value
            .Top = TopAmt
            .Visible = True
            .Tag = "AssignedActions"
            .Value = 0
        
        Set objMyEventClass.tbEvents = newTxtAssigned
        
        End With
        
        Set newTxtMPA = usrProductionReporting.Controls.Add(bstrProgID:="Forms.Textbox.1", Name:="Textbox" & i)
        With newTxtMPA
            .Name = i & "txtMPA" & sht.Range("A" & StartingRow + i).Value
            .Value = sht.Range("B" & StartingRow + i).Value
            .Top = TopAmt + 6
            .Visible = True
            .Tag = "MPA"
        End With
        
        Set newLblMinutes = usrProductionReporting.Controls.Add("Forms.Label.1")
        With newLblMinutes
            .Name = i & "lblMPA" & sht.Range("A" & StartingRow + i).Value
            .Top = TopAmt + 8
            .Visible = True
            .Caption = "minutes per action"
            .Tag = "LabelMPA"
        End With
        
        Set newTxtMax = usrProductionReporting.Controls.Add(bstrProgID:="Forms.Textbox.1", Name:="Textbox" & i)
        With newTxtMax
            .Name = i & "txtMaxActions" & sht.Range("A" & StartingRow + i).Value
            .Top = TopAmt
            .Visible = True
            .Tag = "MaxActions"
        End With
        
        Set newTextA2A = usrProductionReporting.Controls.Add(bstrProgID:="Forms.Textbox.1", Name:="Textbox" & i)
        With newTextA2A
            .Name = i & "txtAvailAssign" & sht.Range("A" & StartingRow + i).Value
            .Top = TopAmt
            .Visible = True
            .Tag = "AvailableToAssign"
        End With
        
        TopAmt = TopAmt + newTxtAssigned.Height
    Next
    
Call mdlControlActions.GlobalControlProperties

End Sub

From what I can gather, I created a Class Module with the following:

Public WithEvents tbEvents As MSForms.TextBox

Private Sub tbEvents_Change()
    mdlCalculations.DynamicMinutes
End Sub

I don't throw an error but it never reaches the Class Module to create the Change Event so the sub is never executed. I do realize that I created the textbox as Could someone help me? I've created textboxes dynamically a few different ways but I've never tried to add the event and I have no idea how I should even be thinking about it. Any assistance is greatly appreciated!

 

[Qwest336]

One thing of note...the result of some of my textboxes is that they will have spaces in the name. Is that an issue? It allows me to create them that way and get values but thinking about it I wonder if that causes an issue programatically.

 

[mikerickson]

You can do this with Class modules.
First let me express surprise that you can create run-time TextBoxes with spaces in their names. It doesn't effect this answer, but is suprising.

First create a class module in the VB Editor. It will automatically be named Class1.
Then put this code in that class module.

' in code module for Class1

Public WithEvents myTextbox As MSForms.TextBox

Private Sub myTextbox_Change()
    MsgBox myTextbox.Name & " changed."
End Sub

The Change event is only for demonstration.

Then for each of the textboxes you create at run-time, you must create an instance of Class1 and set the .myTextBox property to that newly created TextBox.
And then store them in a collection.
This code does all that in the Initialize Event of the userform.

' in userform's code module

Dim RunTimeTextBoxes As Collection

Private Sub UserForm_Initialize()
    Dim i As Long, newBox As MSForms.TextBox
    Dim NewBoxClass As Class1
    Set RunTimeTextBoxes = New Collection
    
    For i = 1 To 4
        Set newBox = Me.Controls.Add("forms.TextBox.1")
        newBox.Top = 5 + 30 * (i - 1)
        newBox.Left = 10 + 50 * (i - 1)
        newBox.Width = 100
        
        Set NewBoxClass = New Class1
        Set NewBoxClass.myTextbox = newBox
        RunTimeTextBoxes.Add Item:=NewBoxClass
    Next i
    
    Set NewBoxClass = Nothing
End Sub

Note that all of the usual events can be used (in the class module) with the textbox except the Enter, Exit, BeforeUpdate and AfterUpdate events.

 

[Qwest336]

You can do this with Class modules.
First let me express surprise that you can create run-time TextBoxes with spaces in their names. It doesn't effect this answer, but is suprising.

First create a class module in the VB Editor. It will automatically be named Class1.
Then put this code in that class module.

' in code module for Class1

Public WithEvents myTextbox As MSForms.TextBox

Private Sub myTextbox_Change()
    MsgBox myTextbox.Name & " changed."
End Sub

The Change event is only for demonstration.

Then for each of the textboxes you create at run-time, you must create an instance of Class1 and set the .myTextBox property to that newly created TextBox.
And then store them in a collection.
This code does all that in the Initialize Event of the userform.

' in userform's code module

Dim RunTimeTextBoxes As Collection

Private Sub UserForm_Initialize()
    Dim i As Long, newBox As MSForms.TextBox
    Dim NewBoxClass As Class1
    Set RunTimeTextBoxes = New Collection
   
    For i = 1 To 4
        Set newBox = Me.Controls.Add("forms.TextBox.1")
        newBox.Top = 5 + 30 * (i - 1)
        newBox.Left = 10 + 50 * (i - 1)
        newBox.Width = 100
       
        Set NewBoxClass = New Class1
        Set NewBoxClass.myTextbox = newBox
        RunTimeTextBoxes.Add Item:=NewBoxClass
    Next i
   
    Set NewBoxClass = Nothing
End Sub

Note that all of the usual events can be used (in the class module) with the textbox except the Enter, Exit, BeforeUpdate and AfterUpdate events.

Thank you for the quick response, Mike.

I was also surprised it allowed me to once I started debugging and realized what I was doing. I might go in and strip the spaces before creating them just to ensure that's not another issue somewhere down the line.

I tried the code you input and a couple of things to note:

1) It created the 4 new textboxes on my userform but when I input values, it never displayed the Message Box, so I guess it didn't make it there.
2) I'm creating the textboxes in a module as opposed to on the Initialize Event. This is to allow for growth of the form so I need to delete the controls and repopulate them on the change event of a combobox.

I'm going to take what you did above and try to adapt it to work in the module and see what happens.

 

[Qwest336]

...

Mike...you are AMAZING. Your post showed me exactly where I was going wrong. Here are the resulting changes (Your additions are commented):

NOTE: Nothing is added in the Userform_Initialize Event.

The CreateControl sub is now as follows:

Public RunTimeTextBoxes As Collection 'Your Addition, but publicly declared

Sub CreateControl()
    Dim newLblMain, newLblMinutes As MSForms.Label
    Dim newTxtMPA, newTxtMax, newTextA2A As MSForms.Control 'newTxtAssigned
    Dim i As Integer, TopAmt
    Dim sht As Worksheet
    Dim LastRow As Long
    Dim NumberOfLabels As Long
    Dim StartingRow As Long
    Dim MaxControls As Long

Dim newTxtAssigned As MSForms.TextBox 'Your addition, with my object name
Dim NewBoxClass As Class1 'Your addition
Set RunTimeTextBoxes = New Collection 'Your addition

Set sht = ThisWorkbook.Worksheets("Pivot")

    LastRow = sht.Range("ActionList").Rows.Count + 2 'Adding two rows to account for the filters in the pivot
    NumberOfLabels = LastRow - 3
    StartingRow = 4
'Number of controls on the userform
    MaxControls = 16


Call mdlControlActions.DeleteControls

'Sets the Top location for the first control after the 4 static controls.  All others will be calculated from this one.
    TopAmt = 242

    For i = 0 To NumberOfLabels - 1
        Set newLblMain = usrProductionReporting.Controls.Add("Forms.Label.1")
        With newLblMain
            .Name = i & "lblMain" & sht.Range("A" & StartingRow + i).Value
            .Top = TopAmt + 6
            .Visible = True
            .Caption = sht.Range("A" & StartingRow + i).Value & ":"
            .Tag = "LabelMain"
        End With

        Set newTxtAssigned = usrProductionReporting.Controls.Add(bstrProgID:="Forms.Textbox.1", Name:="Textbox" & i)
        With newTxtAssigned
            .Name = i & "txtAssigned" & sht.Range("A" & StartingRow + i).Value
            .Top = TopAmt
            .Visible = True
            .Tag = "AssignedActions"
            .Value = 0
      
        Set NewBoxClass = New Class1 'Your addition
        Set NewBoxClass.myTextbox = newTxtAssigned 'Your addition with my object name
        RunTimeTextBoxes.Add Item:=NewBoxClass 'Your addition
      
      
        End With
      
        Set newTxtMPA = usrProductionReporting.Controls.Add(bstrProgID:="Forms.Textbox.1", Name:="Textbox" & i)
        With newTxtMPA
            .Name = i & "txtMPA" & sht.Range("A" & StartingRow + i).Value
            .Value = sht.Range("B" & StartingRow + i).Value
            .Top = TopAmt + 6
            .Visible = True
            .Tag = "MPA"
        End With
      
        Set newLblMinutes = usrProductionReporting.Controls.Add("Forms.Label.1")
        With newLblMinutes
            .Name = i & "lblMPA" & sht.Range("A" & StartingRow + i).Value
            .Top = TopAmt + 8
            .Visible = True
            .Caption = "minutes per action"
            .Tag = "LabelMPA"
        End With
      
        Set newTxtMax = usrProductionReporting.Controls.Add(bstrProgID:="Forms.Textbox.1", Name:="Textbox" & i)
        With newTxtMax
            .Name = i & "txtMaxActions" & sht.Range("A" & StartingRow + i).Value
            .Top = TopAmt
            .Visible = True
            .Tag = "MaxActions"
        End With
      
        Set newTextA2A = usrProductionReporting.Controls.Add(bstrProgID:="Forms.Textbox.1", Name:="Textbox" & i)
        With newTextA2A
            .Name = i & "txtAvailAssign" & sht.Range("A" & StartingRow + i).Value
            .Top = TopAmt
            .Visible = True
            .Tag = "AvailableToAssign"
        End With
      
        TopAmt = TopAmt + newTxtAssigned.Height
    Next
  
Call mdlControlActions.GlobalControlProperties

    Set NewBoxClass = Nothing 'Your addition
  
End Sub

2) The Class Module is the same:

Public WithEvents myTextbox As MSForms.TextBox

Private Sub myTextbox_Change()
    mdlTimeCalculations.DynamicMinutes  'Your addition but you had the MsgBox here for testing
End Sub

Works like a DREAM. Dynamically calculates my minutes as the numbers are entered. You have no idea how much time you've saved me!

 

[mikerickson]

The newly created textboxes have to be made arguments of newly created Class objects as soon as they are created and they need to be put in a Collection in the userform at that time.