Assign on-click VBA function to a dynamically created button on Excel Userform

calendar_today Asked Feb 19, 2009
thumb_up 7 upvotes
history Updated April 16, 2026

Question posted 2009 · +5 upvotes

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?

Accepted answer +7 upvotes

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.

Top vba Q&A (6)

+7 upvotes ranks this answer #56 out of 81 vba solutions on this site .
vba