Menu
I'm creating buttons dynamically on an Excel userform with the following code:
With Me.CurrentFrame.Controls.Add("Forms.CommandButton.1")
.Caption = "XYZ"
.name = "AButton"
.Font.Bold = True
.ForeColor = &HFF&
... blah blah blah
End With
I'd like to assign a function to run when these buttons are clicked, but I can't find a straightforward way to do this since there's no property as part of the button itself.
Is there a way to do this using the above idiom? Should I be going about this whole thing in a different way?
399
Select the Button Form Control from the menu. Right click and hold the mouse then drag and release to create your button. The Assign Macro window will pop up and you can select the VBA procedure you want to run from the button. Press the OK button.
Add Buttons to the UserFormIn the Toolbox, click on the CommandButton button. On the UserForm, click at the bottom left, to add a standard sized CommandButton. With the new CommandButton selected, double-click on the Name property in the Properties window.
VBA ActiveX CommandButton Control on the UserForm Go To Developer Tab and then click Visual Basic from the Code or Press Alt+F11. Go To Insert Menu, Click UserForm. Please find the screenshot for the same.
You need to dynamically create code / event handlers for each button.
It take a bit of doing - see here: http://navpadexcel.blogspot.com/2006/11/httpwwwcpearsoncomexcelvbehtm.html
A better way might be to create a bunch of buttons on the form (as many as you think you'll need) ahead of time. Create the event handler code as well. Make them all hidden initially.
Then when your form opens you can dynamically change the button captions, make them visible and move them around. The event code you created initially will be linked to the activated buttons as expected.
139
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
