[VBA] How to Dynamically Add Controls to a UserForm and Handle Events

Introduction

In VBA UserForms, developers typically place controls manually during the design phase. However, there are times when you want to determine the number or type of controls at runtime—for example, “automatically generating the necessary number of buttons based on the number of data rows in a sheet.”

By using the Controls.Add method in VBA, you can add new controls via code even after the form is already displayed. Furthermore, by combining this with Class Modules, you can handle events (like clicks) for these dynamically added controls.

This article explains a powerful technique to dynamically add command buttons to a UserForm and assign events to each of them.

Overview of Implementation

The implementation involves three main steps:

1. Add Controls: Use the Controls.Add method to generate new controls on the form.
2. Create a Class Module: Create a blueprint (Class) to “monitor” the events of the dynamically added buttons.
3. Bind Events: Create an “instance” of the class for each added button to enable event handling.

VBA Sample Code: Dynamic Buttons with Event Handling

In this example, when the UserForm itself is clicked, the code dynamically generates five buttons (A, B, C, D, E). When any of these buttons is clicked, a message box displays its caption.

Step 1: Create the Class Module

First, create a class to detect the button click event.

1. In the VBA Editor, select Insert > Class Module.
2. In the Properties window, change the (Name) to DynamicButtonHandler.
3. Paste the following code:

Class Module: DynamicButtonHandler

' --- Declare the button that this class will monitor ---
Public WithEvents CreatedButton As MSForms.CommandButton

' --- Executed when the click event occurs on the monitored button ---
Private Sub CreatedButton_Click()
    ' Show the button's caption in a message box
    MsgBox "Clicked button: " & CreatedButton.Caption, vbInformation
End Sub

Step 2: Write the UserForm Code

Next, write the code in the UserForm to add the controls and utilize the class.

UserForm Code

' --- Collection to hold instances of the created class ---
Private ButtonHandlers As Collection

' Event to generate buttons when the form is clicked
Private Sub UserForm_Click()
    Dim i As Long
    Dim buttonCaptions As Variant
    Dim newButton As MSForms.CommandButton
    Dim handler As DynamicButtonHandler
    
    ' Do nothing if buttons are already created
    If Me.Controls.Count > 0 Then Exit Sub
    
    ' Prepare button captions in an array
    buttonCaptions = Array("A", "B", "C", "D", "E")
    
    ' Initialize the collection to hold handlers
    Set ButtonHandlers = New Collection
    
    ' --- Dynamically add buttons in a loop ---
    For i = LBound(buttonCaptions) To UBound(buttonCaptions)
        ' 1. Generate a new command button using Controls.Add
        '    Syntax: Controls.Add("ProgID", "Name")
        Set newButton = Me.Controls.Add("Forms.CommandButton.1", "DynamicBtn" & i)
        
        ' 2. Set button properties
        With newButton
            .Caption = buttonCaptions(i)
            .Width = 40
            .Height = 30
            .Top = 20
            .Left = 15 + (i * (.Width + 5))
        End With
        
        ' 3. Create an event handler (class instance) for the button
        Set handler = New DynamicButtonHandler
        
        ' 4. Set the created button to the handler to bind them
        Set handler.CreatedButton = newButton
        
        ' 5. Add handler to the collection to keep the instance alive
        '    (If you don't do this, the handler is destroyed when the Sub ends)
        ButtonHandlers.Add handler
    Next i
End Sub

Explanation of the Code

Me.Controls.Add("Forms.CommandButton.1", ...)

This is the core method for dynamically adding controls.

• Controls.Add: The method used to add a control.
• Argument 1: Specifies the class name (ProgID) of the control as a string. "Forms.CommandButton.1" represents a CommandButton, and "Forms.TextBox.1" represents a TextBox.
• Argument 2: Specifies the name of the control. It is good practice to give it a unique name so you can reference it later.

Class Modules and WithEvents

For manually placed buttons, you can easily write events like Private Sub Button_Click(). However, you cannot do this for dynamically added controls because they don’t exist at design time.

To solve this, we use a Class Module with the WithEvents keyword. This creates a mechanism to “listen” for events from a specific object variable (CreatedButton in our example).

Persisting Instances (The Collection)

The class instance (handler) created inside UserForm_Click() is a local variable. Normally, it would be destroyed (and the event connection lost) as soon as the UserForm_Click procedure finishes.

To prevent this, we declare a Collection variable (ButtonHandlers) at the module level. By adding each handler instance to this collection, we ensure they remain in memory as long as the form is open.

Summary

In this article, we covered an advanced and flexible technique: dynamically adding controls to a UserForm and handling their events using Class Modules.

• Use Controls.Add("ClassName", "Name") to generate controls at runtime.
• Use Class Modules and WithEvents to handle events for dynamic controls.
• Use a Collection or Array to keep the class instances alive in memory.

Mastering this method allows you to greatly expand the possibilities of your applications, such as changing the number of input fields based on data volume or switching displayed buttons according to user selection.