How To: Assign a Macro to a Button or Shape
by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2009.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
In recent posts, I’ve discussed recording macros, fixing recorded macros, and even using someone else’s macros. To run a macro, you have a few choices.
- Tools menu > Macro > Macros, select the Macro, and click Run
- Shortcut Key combo Alt+F8, select the Macro, and click Run
- Run Macro button on the Visual Basic toolbar, select the Macro, and click Run
- Click in the macro with the mouse and press F5
None of these techniques are particularly elegant, and you wouldn’t want to email a file with a few macros to a colleague and tell them, “Okay, go to the Tools menu, select Macro, then select Macro, then click on MyFirstMacro in the list, and click Run.” It would be nice to just tell them, “Click the First Macro button.”
You can assign a macro to a few types of objects:
- Forms Menu Button or Shape
- ActiveX Button
- Menu or Toolbar Button
In this post we’ll look at using a Forms menu button to run our macros. We’ll use this simple macro for our example.
Sub HelloWorld() MsgBox "Hello, World! ", vbExclamation End Sub
When it is run, we see a simple message.
Forms Menu Controls
First make the Forms toolbar visible. Like so many other things in Excel, there are several ways to accomplish this.
- Tools menu > Customize > Toolbars tab, check the box in front of Forms
- View menu > Toolbars, select Forms from the list
- Right click on the menu and toolbar area, choose Forms from the list
The Forms toolbar will appear.
Select the Button button, then draw a rectangle in the worksheet where you want to place the button (you can move and resize it later). The Assign Macro dialog pops up with a list of macros in the active worksheet for you to select from.
Select a macro and click OK, or Cancel (you can select a macro at a future time). You can edit the text of the button after you dismiss the dialog, or right click on the button any time.
You can assign a macro to any of the Forms toolbar controls.
Shapes
You can assign a macro to any shape (including any chart) on a worksheet. Right click on the shape, choose Assign Macro, and follow the steps above.
Possibly Related Posts:
- How To: Assign a Macro to a Toolbar or Menu
- How To: Assign a Macro to an ActiveX Control
- How To: Record Your Own Macro
- Forms Controls and ActiveX Controls in Excel
- How To: Use Someone Else’s Macro
- My Customized Excel Toolbars
- How To: Fix a Recorded Macro
- Sample Parallel Coordinate Chart
- Installing an Add-In in Excel 2007
- A Belated Review of Excel 2007
Posted: Wednesday, March 12th, 2008 under VBA.
Comments: 17
Comments
Comment from Tony
Time: Thursday, March 13, 2008, 7:39 am
Great post! I am a big fan of assigning macros to a button so the user can easily run ‘em without having to go through the menu.
One thing that typically trips people up is the default security setting for Excel. Many times, if you send someone an .xls file with a macro, they either need to approve the sender or set their security lower. Otherwise, they won’t be able to run the macro. I’m sure most readers know this, but I think it’s worth bringing up.
Comment from Jon Peltier
Time: Thursday, March 13, 2008, 8:59 am
Tony – Good point. One of these days I’ll blog about macro security.
Comment from Andras
Time: Tuesday, March 18, 2008, 9:41 am
John,
I’m very helpful for your site – it gave me many help in my job.
In addition your blog is also very useful – but as you’ve wrote, it took a bit time to find it.
However, please let me ask a question regarding to this blog entry.
Is there any way to assign an UDF to a button or to a menu/toolbar item in Excel?.
When I’ve tried to assign to a toolbar some of my UDFs stored in my personal.xls, but only the simple VBA routines appear, not the UDFs.
I’m currently using MS Excel 2002.
I’d be very much appreciated if you could share us your experiences in this.
Thank you for your help in advance
Kind regards,
Andras Ujszaszy
Comment from Jon Peltier
Time: Tuesday, March 18, 2008, 1:41 pm
Hi Andras, By definition a UDF is used in a cell or in another VBA procedure and simply returns a value. When you click on a button you are not so much expecting to get a value as having some action perfomred. How would your UDF work from a button click?
Comment from Sergey
Time: Sunday, December 14, 2008, 8:57 am
Hi Jon,
thank you for making this great blog!
I have a question about assigning macro to control button:
“The Assign Macro dialog pops up with a list of macros in the active worksheet for you to select from.” Issue is it doesn’t pop up in my 2003 excel, moreover in right-click menu there is no command “assign macro”. If I create picture or object (not control button) – I can see this command in menu.
Could you please advise why it doesn’t work with control buttons?
Sergey
Comment from Jon Peltier
Time: Sunday, December 14, 2008, 10:06 am
Hi Sergey -
This tip relates to the controls from the Forms toolbar, which behave much like inserted pictures or shapes.
Controls Toolbox objects are a different species altogether. For those you need to follow the procedure in How To: Assign a Macro to an ActiveX Control.
Comment from Victor
Time: Tuesday, December 16, 2008, 1:03 pm
Hey there Jon,
Really appreciate your site. In fact I’ve been using the Button to send selected graphs over into PowerPoint (based on an article by yourself). When one day I ran into some trouble. I thought I’d spice up the buttons by replacing them with a Shape (and some fancy coloring/shading). Trouble is, now I can’t shoot em over to PPT.
Think you could tell me what I’m missing?
Best regards
Victor
Comment from Jon Peltier
Time: Tuesday, December 16, 2008, 1:54 pm
Victor -
If the button is a Forms toolbar button, it interacts with a macro the same way as a shape (right click > Assign Macro…). If it’s a controls toolbox button, the protocol is different, as described in How To: Assign a Macro to an ActiveX Control.
Or do you mean you added shapes to the chart? If you select the chart, then insert the shape, the shape is part of the chart. If you just added the shape, it’s part of the worksheet, not the chart, and grouping the chart to the shape makes another shape, which the chart objects code will not recognize.
Comment from Victor
Time: Tuesday, December 16, 2008, 3:48 pm
Actually, I simply inserted a shape into a worksheet, made a chart just somewhere on the worksheet, assigned a macro to the “Shape” (not button ;) that is supposed to transfer my chart but I get my error alert saying “please select a chart” when I click on the macro associated shape.
If I use a button from the form control, everything is a ok .
The problem I believe lies with the following ; when I select a chart to transfer, then hit the “Shape” that I’ve assigned a macro to (the macro doing the transferring) I believe excel “deselects” the prior selected chart and instead selects the shape then activates the macro causing the above mentioned error.
I would use a button instead of the “shape” but the shape looks cool :).
Comment from Jon Peltier
Time: Tuesday, December 16, 2008, 4:54 pm
Intriguing. In Excel 2003, I created a column chart and wrote this code:
Sub Sample()
If ActiveChart Is Nothing Then
MsgBox "Select a chart"
Else
ActiveChart.ChartType = xlLine
End If
End Sub
Then I inserted a Forms button and a shape, and applied this macro to both. If I select the chart and click the button, the chart type changes. If I select the chart, change it back to a column chart, and click on the shape, again the chart type changes. The chart remains selected, though after the macro runs it is selected with white handels, meaning the shape containing the chart is actually what is selected.
Comment from Ricardo
Time: Thursday, January 22, 2009, 5:48 am
I`d like to assign a macro(MouseMove) to a shape like you show above, but whan i click on the shape and chosse Assign Macro, the event assign is always the click event. So my doubt is, could i assing another event like MouseMove instead of Click ?
Tks for your help.
Comment from Jon Peltier
Time: Thursday, January 22, 2009, 7:02 am
Excel proposed a default macro name that mimics a click event (e.g., Rectangle1_Click), but it isn’t really an event in the sense that a command button or other ActiveX control has events it can respond to. You can only assign the name of an existing macro.
In the Excel object model, shapes expose no events that your code can respond to. The worksheet itself has a limited set of events you can code against, while charts and AcitveX controls have a rich set of events you can exploit.
Comment from Mei
Time: Wednesday, March 18, 2009, 11:46 pm
Hi Jon
I encounter sporadic problems when assigning macros to objects – i.e. the macros fail to run when the objects are re-positioned on the same worksheet. Sometimes they work, and sometimes they don’t! There doesn’t seem to be anything wrong with the macros themselves.
Would you happen to know why or have a solution for this?
Many thanks.
Mei
Malaysia
Comment from Jon Peltier
Time: Thursday, March 19, 2009, 5:45 am
Mei -
I have never experienced this problem exactly. What version of Excel are you using? There are a couple funny things about assigning macros to shapes in Excel 2007. If that’s your version I would like to try to replicate the problem.
Comment from Mei
Time: Thursday, March 19, 2009, 6:59 pm
Hi Jon,
Thank you for your reply. Indeed I am using Excel 2007, on a Vista OS.
It’s a simple macro to copy & paste by appending to a columnn of values. I assigned this macro to a shape that I drew somewhere on the worksheet. When I click the enabled shape, it works fine, however, the moment i move the shape to another part of the worksheet – even if it’s near by, the macros become very unstable. They work some of the time, and fail altogether at others.
It’s quite strange.
Many thanks.
Mei.
Comment from Jon Peltier
Time: Thursday, March 19, 2009, 10:23 pm
Could you use a regular button as opposed to a shape for this? I’ve heard that using buttons (use Forms toolbar buttons, which nominally work just like shapes) is more reliable than using shapes.
Comment from Mei
Time: Thursday, March 19, 2009, 10:53 pm
Thanks Jon, will try it out and share any further problems if I encounter them.
Cheers.
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.