To add a control event dynamically in an Excel form; you need to first add the event(s) in a class module. For my example, I am going to add a class module named clsTEST with one event, btn_click()
'#### CLASS NAMED clsTEST
Public WithEvents btn As MSForms.CommandButton
Public frm As UserForm
Dim iCount As Long
Private Sub btn_Click()
iCount = IIf(iCount < 1, 1, iCount + 1)
btn.Caption = "Count " & Str(iCount)
End Sub
'### END CLASS
As you can see, the only thing this will do is set the caption on the button to then number of times you clicked it.
Next, in the form code enter the following:
Dim mColButtons As New Collection '## SET A NEW COLLECTION
Private Sub UserForm_Activate()
'
Dim btnEvent As clsTEST
Dim ctl As MSForms.Control
'
Set ctl = Me.Controls.Add("Forms.CommandButton.1")
'
With ctl
.Caption = "XYZ"
.Name = "AButton"
END With
'
Set btnEvent = new clsTEST
Set btnEvent.btn = ctl
set btnEvent.frm = Me
'
mColButtons.add btnEvent
'End Sub
When you activate the form, it will create a button. every time you click on the button the caption will change.