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

Question

I would like to add a Control and an associated event at runtime in Excel using VBA but I don't know how to add the events.

I tried the code below and the Button is correctly created in my userform but the associated click event that should display the hello message is not working.

Any advice/correction would be welcome.

Dim Butn As CommandButton
Set Butn = UserForm1.Controls.Add("Forms.CommandButton.1")
With Butn
    .Name = "CommandButton1"
    .Caption = "Click me to get the Hello Message"
    .Width = 100
    .Top = 10
End With

With ThisWorkbook.VBProject.VBComponents("UserForm1.CommandButton1").CodeModule
    Line = .CountOfLines
    .InsertLines Line + 1, "Sub CommandButton1_Click()"
    .InsertLines Line + 2, "MsgBox ""Hello!"""
    .InsertLines Line + 3, "End Sub"
End With
UserForm1.Show

Answer 1

The code for adding a button at run time 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 Explicit


Dim ButArray() As New Class2

Private 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
    Next
End Sub

---

Now U need to add a Class Module to your Code for the project..Please remember its class module not Module.And put in following simple Code( In my case the class name is Class2)-

---

Public WithEvents butEvents As MSForms.CommandButton

Private Sub butEvents_click()

    MsgBox "Hi Shrey"

End Sub

---

Thats it. Now run it