Assign macros to dynamically created form elements

[Joepy]

Hello,

I have a form where all elements (dropdowns, checkboxes, etc.) are
created dynamically from the values of an excel sheet.

How can I assign a macro to a form element? When the form element is
actually drawn in the VBA editor, I can use a "Private Sub
btn_Info_Click()", but this is obvoiusly not possibe in this case.

Does anyone knwo a solution?

Regards,
Joepy

 

[JE McGimpsey]

One way:

Public Sub Macro1()
ActiveSheet.Shapes("Button 1").OnAction = "MyMacro"
End Sub

 

[Bob Phillips]

.OnAction property?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Joepy]

Thanks for the mails I received so far.
Unfortunately, none of the solutions work:

Maybe you know what I mean when you look at the following example:

Private Sub UserForm_Initialize()

Set MyButton = Me.Controls.Add("Forms.CommandButton.1", "button01")

MyButton.Top = 25
MyButton.Left = 12
MyButton.Width = 72
MyButton.Height = 24
MyButton.Caption = "Click Me"
'MyButton.OnAction = Test

End Sub

This creates a button on the form. The last line (where I used
'onAction') should assign a macro ("Test") to the button. However,
'onAction' doesn't work. I really hope there is a way I can get this
to work.

Any help is really appreciated.

Joepy

 

[Bob Phillips]

Joepy,

I don't thoink we appreciated that you meant a button on a worksheet.

A different approach.

Add a class module, and add the code belows. Name the clas module clsButtons

'===============================================

Public WithEvents Buttons As MSForms.CommandButton

Private Sub ButtonS_Click()
'add your specific code here
MsgBox "You clicked: " & Buttons.Caption
End Sub


And finally, create your button this way.


'===============================================

Dim myButton

Set myButton = Me.Controls.Add("Forms.CommandButton.1", "button01")
Set formButton = New clsButtons
Set formButton.Buttons = myButton
collBtn.Add formButton

myButton.Top = 25
myButton.Left = 12
myButton.Width = 72
myButton.Height = 24
myButton.Caption = "Click Me"

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)