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, such as the 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.
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.