Well, Mark, depends on how you see it. If it was fully compiled, then I guess that yes, that should be the way to go, but, I have noticed that VBA makes some re-compilations, even at run time !
The following code worked ok. It adds buttons, and uses a class module (As I learned from Bullen) to get the event to fire.
The commented code almost worked, it added the button, and added the event code to the userform (The 5 is because I had only 3 sheets, the ThisWorkbook and the Userform), but, the button didn't respond to the event, altough the name was it ! and, I was getting an error when I tried to add another control.
This goes in the Userform's module. At design, it should only has one button (CommandButton1), and put it somewhere on the right, so it won't "interfere" with the added ones.
<pre>Dim Tp As Long
Dim Ctr As Long
Dim MyCol As Collection
Private Sub CommandButton1_Click()
'Dim UF As Object
'Dim i As Long
'Dim StrCode As String
Dim Btn As Object 'MSForms.CommandButton
Dim Mycls As New clsBtn
'Set UF = ThisWorkbook.VBProject.VBComponents(5)
Ctr = Ctr + 1
Set Btn = Me.Controls.Add("Forms.CommandButton.1", "MyBtn" & Ctr)
With Btn
.Caption = .Name
.Left = 10
.Top = Tp
.Width = 60
.Height = 18
End With
Tp = Tp + 22
Set Mycls.Btn = Btn
Mycls.Nm = Btn.Name
MyCol.Add Mycls
' StrCode = "Private Sub " & Btn.Name & "_Click()" & vbCrLf
' StrCode = StrCode & " MsgBox " & Chr(34) & "Clicked on " & Btn.Name & Chr(34) & vbCrLf
' StrCode = StrCode & "End Sub"
' With UF.CodeModule
' i = .CountOfLines
' 'MyText is a variant which will hold the answer the user pressed
' .InsertLines i + 1, StrCode
' End With
End Sub
Private Sub UserForm_Initialize()
Tp = 20
Ctr = 0
Set MyCol = New Collection
End Sub</pre>
and the following is the class module, named clsBtn
<pre>Public WithEvents Btn As MSForms.CommandButton
Public Nm As String
Private Sub Btn_Click()
MsgBox "Clicked on button: " & Nm
End Sub</pre>