Dynamically add controls with events at runtime

Hello,

I want to add new buttons to an existing userform at runtime and link event handler to them so that the buttons do something.

I've tried several ways that I've found online and managed to create the button and to programmatically add the code for the click event but when i click the button nothing happens. The button and click event sub code is created fine.

I have added the reference to Microsoft VBA Extensibility but something is missing.

Please, I am completely stuck and would appreciate any help! Thank you.

Here's the code I have so far:

Dim myCmdObj As CommandButton
Dim i As Integer

Private Sub CommandButton1_Click()

Dim NName As String

i = i + 1

' Set the name for the button
NName = "cmdAction" & i

' Add button
Dim N As Long
Set myCmdObj = frmMain.Controls.Add("Forms.CommandButton.1", NName, Visible)

With myCmdObj
.Caption = NName
.Left = 20 * i
.Width = 20
.Top = 20
End With

' Inserts code for the button
With ThisWorkbook.VBProject.VBComponents(5).CodeModule
N = .CountOfLines
.InsertLines N + 1, "Private Sub " & NName & "_Click()"
.InsertLines N + 2, vbNewLine
.InsertLines N + 3, vbTab & "lblTest.Caption = ""BUTTON CLICKED"""
.InsertLines N + 5, "End Sub"
End With
End Sub

Please help me!!

The reason that I am not creating the control at design time and hiding it until it is needed is because I don't know how many controls I'm going to need and I don't want to put an upper limit on how many can be created.

Also I discovered that if the subroutine is called by the nama eof the CommandButton variable NOT the commandbutton name, then it works.

But this implies that the declaration has to be global. For creating more than one control i would have to declare more than one CommandButton objects at design time. But then I'm still left with the same problem of how to create as many controls as needed at runtime without specifying an upper limit at design time.

For example:

Dim WithEvents NewButton as CommandButton 'Cannot declare this inside a procedure or error

private sub cmdAction_Click()

Set NewButton = Controls.Add("Forms.CommandButton.1")

NewButton.Name = "ButtonName"

end sub

Private Sub NewButton_Click()

lblTest.Caption = "THIS EVENT IS FIRED WHEN BUTTON IS CLICKED"
End Sub

Private Sub ButtonName_Click()

lblTest.Caption = "this event is NOT fired when button is clicked"
End Sub

It really doesn't make much sense that it would work like this, but that's how it is. What I need is a way to make the click event fire with the BUTTON.NAME attribute and NOT with the actual name of the control variable.

Can somebody please help me!! any input is appreciated!!

Yes, the second link is what I needed!

Now I can create multiple buttons at runtime and deal with them all with the same click event handler and refer to each of them individually using their name attribute. No need to add subprocedures to the event code programatically.

Thank you so much!