How to reference a userform command button click event when the button was created dynamically?

[bkirtland]

Hi all,

Novice VBA user here. I'm a structural engineer and working to create a program that helps evaluate the demands on a pile foundation group. So far, it has two user forms - the first form asks the user how many piles the group contains, and then a "next" or "cancel" button. When the "next" button is pressed, it pulls up a second user form that is created based on the number of piles entered in the previous form. Here's the code I wrote that creates the form:

Private Sub UserForm_Initialize()
'Set up the userform upon when it opens

Dim pilelabel, xcoordlabel, ycoordlabel, pilex, piley, Next2button, Cancel2button As Object, pilecounter As Integer

'Set size of userform
PileGroupInput2.Height = 100 + 18 * NumPiles
PileGroupInput2.Width = 160 + 50 + 50

'Create pile # labels corresponding to the number of piles indicated on the previous input form.
For pilecounter = 1 To NumPiles
    Set pilelabel = PileGroupInput2.Controls.Add("Forms.Label.1", "Pile" & pilecounter, True)
    With pilelabel
        .Caption = "Pile #" & pilecounter
        .Left = 30
        .Width = 50
        .Top = 10 + 18.65 * pilecounter
    End With
Next

'Create x-coord and y-coord labels
Set xcoordlabel = PileGroupInput2.Controls.Add("Forms.Label.1", "XCoordLabel", True)
With xcoordlabel
    .Caption = "X-Coord. (ft)"
    .Left = 85
    .Width = 50
    .Top = 10
End With

Set ycoordlabel = PileGroupInput2.Controls.Add("Forms.Label.1", "YCoordLabel", True)
With ycoordlabel
    .Caption = "Y-Coord. (ft)"
    .Left = 160
    .Width = 50
    .Top = 10
End With

'Create x- and y-coord input boxes for each pile
pilecounter = 1 'reset pile counter
For pilecounter = 1 To NumPiles
    Set pilex = PileGroupInput2.Controls.Add("Forms.TextBox.1", "Pile" & pilecounter & "x", True)
    With pilex
        .Left = 85
        .Width = 50
        .Top = 10 + 18 * pilecounter
    End With
    
    Set piley = PileGroupInput2.Controls.Add("Forms.TextBox.1", "Pile" & pilecounter & "y", True)
    With piley
        .Left = 160
        .Width = 50
        .Top = 10 + 18 * pilecounter
    End With
Next
        
'Create cancel and next buttons
Set Cancel2button = PileGroupInput2.Controls.Add("Forms.CommandButton.1", "Cancel2", True)
With Cancel2button
    .Caption = "Cancel"
    .Left = 30
    .Width = 50
    .Top = 40 + 18 * NumPiles
End With

Set Next2button = PileGroupInput2.Controls.Add("Forms.CommandButton.1", "Next2", True)
With Next2button
    .Caption = "Next"
    .Left = 160
    .Width = 50
    .Top = 40 + 18 * NumPiles
End With

End Sub

The form looks exactly the way I want it, which is awesome. The problem that I'm running into now is that I don't know how to tell the program what to do when the Next2 or Cancel2 buttons are pressed. When creating the first userform (not dynamically, just by hand), I could double click on the Next/Cancel buttons on that form and private subs called Next1_Click() and Cancel2_Click() would be created, and I could write whatever code I needed within those private subs.

I tried just manually writing "Private Sub Next2_Click() / End Sub" but it doesn't seem to work that way, see attached image.

Any help would be greatly appreciated!!

 

[GWteB]

Hi & welcome to MrExcel!

With dynamically constructed userforms you will have to check all the ActiveX controls present and then provide each control with its own event handler.
This can be done relatively easily by means of a class module with the correct code. Each command button, text box or whatever control is then assigned its own instance of that class.

In the example below I have build a combined event object for both the text boxes and the buttons. You could also provide each type of control with its own event handler class. The choice for this depends on how the userform will ultimately look like: how many controls of which type. The code must of course remain clear and readable to facilitate any necessary adjustments or extensions later on.


This goes in a Class module, to be renamed from Class1 to UsfControlEvents:

Option Explicit

Public WithEvents TextBoxGroup As MSForms.TextBox
Public WithEvents CmdBttnGroup As MSForms.CommandButton

Private Type TLocals
    Usf As Object
End Type
Private this As TLocals

Public Sub Init(ByVal argHostUsf As Object)
    Set this.Usf = argHostUsf
End Sub


Private Sub CmdBttnGroup_Click()
    MsgBox "Button " & CmdBttnGroup.Name & " has been clicked"
End Sub

Private Sub TextBoxGroup_Change()
    MsgBox "Textbox " & TextBoxGroup.Name & " has been changed"
End Sub

This goes in the code-behind module of the Userform:
Put this at the top of the Userform module:

Option Explicit

Private Type TLocals
    AllControls()  As New UsfControlEvents
End Type
Private this As TLocals

Put this anywhere in the Userform module:

Private Sub CreateEventHandlers()
    Dim ctrl As MSForms.Control, ControlCount As Long
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "TextBox" Then
            ControlCount = ControlCount + 1
            ReDim Preserve this.AllControls(1 To ControlCount)
            With this.AllControls(ControlCount)
                Set .TextBoxGroup = ctrl
                .Init argHostUsf:=Me
            End With
        ElseIf TypeName(ctrl) = "CommandButton" Then
            ControlCount = ControlCount + 1
            ReDim Preserve this.AllControls(1 To ControlCount)
            With this.AllControls(ControlCount)
                Set .CmdBttnGroup = ctrl
                .Init argHostUsf:=Me
            End With
        End If
    Next ctrl
End Sub

Example of the Initialize procedure:

Private Sub UserForm_Initialize()

    ' ======================
    ' your construction code
    ' ======================

    ' this procedure should be invoked at the end of the Initialize event handler
   CreateEventHandlers
End Sub