How to add Events to dynamically created Controls in Excel using VBA

You can create and add controls at runtime in Excel UserForms. Some controls take data as input, some allow you to select or choose and then there are button controls that perform a task for a "particular event". Here in this post, I am sharing a simple VBA example that shows how to add events to controls created at runtime in a UserForm.

Every control (not just a command button) has events, to perform (or execute) a particular task. When you drag and drop controls on your UserForm, it automatically adds some default events to the control like "Click, DblClick" etc. However, when you create controls dynamically or at runtime, you will have to attach events to the controls explicitly, using WithEvents.

Add a UserForm in Excel

Adding a UserForm in Excel is simple. Open an Excel file and save the file in .xlsm format. Open the VBA editor and open its Project Explorer window. Right click the project and insert a UserForm in the application. See the image.

Add a UserForm in Excel VBA

Now we will create two controls at runtime and add some events to the controls. Do not drag and drop any control on the UserForm.

Option Explicit

Public tbPin As MSForms.TextBox         ' The textbox control.
Dim objMyEventClass As New Class1       ' Create an object of the Class (where we declared the events).

Private Sub UserForm_Initialize()

    ' Create and add the textbox.
    Set tbPin = Me.Controls.Add("Forms.TextBox.1")
    With tbPin
        .Top = 8
        .Left = 10
        .Width = 130
        .name = "thePin"
    End With
    
    Set objMyEventClass.tbEvents = tbPin        ' Attach an event to the textbox.

    ' Create and add the button control.
    Dim btEx As MSForms.CommandButton
    Set btEx = Me.Controls.Add("Forms.CommandButton.1")
    With btEx
        .Top = 50
        .Left = 10
        .Width = 130
        .Height = 25
        .Caption = "Submit"
    End With
    
    Set objMyEventClass.btEvents = btEx         ' Attach at event to the button.
End Sub

I have declared the textbox control (tbPin) as Public, since I’ll be using the object in my Class module. The command button is Private.

After setting the parameters, I am attaching the events to my controls. The events are declared inside the Class Module.

I have also declared an object of the class, using which I’ll access all the public events.

Dim objMyEventClass As New Class1

Add a Class Module in Excel VBA

The Class module is where we’ll add the events for the controls using WithEvents. Usually, we declare the WithEvents modifier in a Class module. Its considered good practice, as the Public Events will be available to all the UserForms and other objects in the application and its easily manageable.

So, let’s insert a Class module. The process is the same as inserting a UserForm. Open the Project Explore, right-click the project and from the insert option, choose Class Module.

Option Explicit

Public WithEvents tbEvents As MSForms.TextBox
Public WithEvents btEvents As MSForms.CommandButton

Private Sub tbEvents_Change()
    If Len(UserForm1.tbPin) > 6 Then
        MsgBox "Max 6 digits only"

        With UserForm1.tbPin
            .Text = ""
            .SetFocus
        End With
    End If
End Sub

Private Sub btEvents_click()
    If Trim(UserForm1.tbPin) <> "" Then
        Call showMyPin(UserForm1.tbPin)
    End If
End Sub

Private Sub showMyPin(pin As String)
    MsgBox "You have entered " & pin
End Sub

There are two events, one for the textbox (Change) and another for the command button (click). To handle these events, I have defined two variables using WithEvents, in the beginning the class.

Public WithEvents tbEvents As MSForms.TextBox
Public WithEvents btEvents As MSForms.CommandButton

Similarly, you can add all the other events (that are available) in your Class module and attach it the controls.

Run the application. Enter a 6-digit value in the textbox and press the button. If you enter more than 6 digits in the box, the textbox’s Change event will show a message and will ask to enter only 6 digits.

The command button’s click event will pick the data entered in the textbox, calls a procedure that will show the value.

Well, that’s it. Thanks for reading. ☺

← PreviousNext →

How to add Events to dynamically created Controls in Excel using VBA

Tools you can use

Add a UserForm in Excel

Add a Class Module in Excel VBA