How to add events to Controls created at runtime in Excel with VBA

The code for adding a button at runtime and then to add events is truly as simple as it is difficult to find out. I can say that because I have spent more time on this perplexity and got irritated more than in anything else I ever programmed.

Create a Userform and put in the following code:

Option ExplicitDim ButArray() As New Class2Private Sub UserForm_Initialize()    Dim ctlbut As MSForms.CommandButton        Dim butTop As Long, i As Long    '~~> Decide on the .Top for the 1st TextBox    butTop = 30    For i = 1 To 10        Set ctlbut = Me.Controls.Add("Forms.CommandButton.1", "butTest" & i)        '~~> Define the TextBox .Top and the .Left property here        ctlbut.Top = butTop: ctlbut.Left = 50        ctlbut.Caption = Cells(i, 7).Value        '~~> Increment the .Top for the next TextBox        butTop = butTop + 20        ReDim Preserve ButArray(1 To i)        Set ButArray(i).butEvents = ctlbut    NextEnd Sub

Now you need to add a Class Module to your code for the project. Please remember it's class module; not Module. And put in the following simple code (in my case the class name is Class2).

Public WithEvents butEvents As MSForms.CommandButtonPrivate Sub butEvents_click()    MsgBox "Hi Shrey"End Sub

That's it. Now run it!

Try this:

Sub AddButtonAndShow()    Dim Butn As CommandButton    Dim Line As Long    Dim objForm As Object    Set objForm = ThisWorkbook.VBProject.VBComponents("UserForm1")    Set Butn = objForm.Designer.Controls.Add("Forms.CommandButton.1")    With Butn        .Name = "CommandButton1"        .Caption = "Click me to get the Hello Message"        .Width = 100        .Top = 10    End With    With objForm.CodeModule        Line = .CountOfLines        .InsertLines Line + 1, "Sub CommandButton1_Click()"        .InsertLines Line + 2, "MsgBox ""Hello!"""        .InsertLines Line + 3, "End Sub"    End With    VBA.UserForms.Add(objForm.Name).ShowEnd Sub

This permanently modifies UserForm1 (assuming you save your workbook). If you wanted a temporary userform, then add a new userform instead of setting it to UserForm1. You can then delete the form once you're done with it.

Chip Pearson has some great info about coding the VBE.

DaveShaw, thx for this code man!

I have used it for a togglebutton array (put a 'thumbnail-size' picture called trainer.jpg in the same folder as the excel file for a togglebutton with a picture in it). In the 'click' event the invoker is also available (by the object name as a string)

In the form:

Dim CreateTrainerToggleButtonArray() As New ToggleButtonClass Private Sub CreateTrainerToggleButton(top As Integer, id As Integer)Dim pathToPicture As StringpathToPicture = ThisWorkbook.Path & "\trainer.jpg"Dim idString As StringidString = "TrainerToggleButton" & idDim cCont As MSForms.ToggleButtonSet cCont = Me.Controls.Add _   ("Forms.ToggleButton.1")With cCont   .Name = idString   .Width = 20   .Height = 20   .Left = 6   .top = top   .picture = LoadPicture(pathToPicture)   End With   ReDim Preserve CreateTrainerToggleButtonArray(1 To id)   Set CreateTrainerToggleButtonArray(id).ToggleButtonEvents = cCont   CreateTrainerToggleButtonArray(id).ObjectName = idString   End Sub

and a class "ToggleButtonClass"

  Public WithEvents ToggleButtonEvents As MSForms.ToggleButton  Public ObjectName As String  Private Sub ToggleButtonEvents_click()  MsgBox "DaveShaw is the man... <3 from your friend: " & ObjectName  End Sub

Now just simple call from UserForm_Initialize

 Private Sub UserForm_Initialize()   Dim index As Integer   For index = 1 To 10     Call CreateTrainerToggleButton(100 + (25 * index), index)   Next index End Sub