add event to controls added in runtime

[Brotha lee]

Hi,

I can not figure this out!

Does anybody knows how you can add events to controls added in runtime on a
userform.

I use the following code to:

myheight = 12
mytop = 70
mywidth = 12
myleft = 12
Set Mycmd = Controls.Add("Forms.CommandButton.1", "Test")
Mycmd.Left = myleft
Mycmd.Top = mytop
Mycmd.Width = mywidth
Mycmd.Height = myheight

 

[Bob Phillips]

Here is a working example

'-----------------------------­------------------------------­------
Sub CreateControlButton()
'-----------------------------­------------------------------­------
Dim oWs As Worksheet
Dim oOLE As OLEObject
Dim myheight, mytop, mywidth, myleft, mycmd
myheight = 12
mytop = 70
mywidth = 12
myleft = 12
Set oWs = ActiveSheet
Set oOLE = _
oWs.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Left:=myleft, Top:=mytop, Width:=mywidth,
Height:=myheight)
With oOLE
.Object.Caption = "Run myMacro"
.Name = "myMacro"
End With
With ThisWorkbook.VBProject.VBComponents(oWs.CodeName).CodeModule
.InsertLines .CreateEventProc("Click", oOLE.Name) + 1, _
vbTab & "If Range(""A1"").Value > 0 Then " & _
vbCrLf & _
vbTab & vbTab & "Msgbox ""Hi""" & vbCrLf & _
vbTab & "End If"
End With
End Sub