How To: Assign a Macro to a Button or Shape
by Jon Peltier
Wednesday, March 12th, 2008
Peltier Technical Services, Inc., Copyright © 2010.
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.
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
- Build an Excel Add-In 6 – Interface for 2003
Posted: Wednesday, March 12th, 2008 under VBA.
Comments: 27
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.
Comment from Razib
Time: Wednesday, December 9, 2009, 9:28 am
Hi I am facing problem to assign macro in bar chart. In fact I am drawing the chart using macro but I need to make some changes such that edit grid lines, lebelling axes, figure name etc. in the bar chart. I tried using assign macro by clicking right on the chart followed by assigning a name and record. After that I did the modifications and stop it. However, it is showing error. I will be grateful if anyone helps.
Comment from Razib
Time: Wednesday, December 9, 2009, 9:34 am
Hi I am trying to convert the text cell to number automatically using macro. But it’s not working. In fact, I start as follows.
start recording >> select the cells>> right click on error to convert to number>> stop recording.
When I ask macro to run it again, it only selects the cells but not converts to number. Hence, I have to do it manually. Moreover, I checked VBA code as well; it doesn’t write anything to convert to number.
Any help will be appreciable.
Comment from Jon Peltier
Time: Wednesday, December 9, 2009, 9:40 am
Razib -
I don’t understand the question about the chart and macro.
To get a macro that converts text to numbers, select the range then start the macro recorder, change the number format from text to a numerical format (if necessary), then use Columns to Text to do the actual conversion.
Comment from razib
Time: Wednesday, December 9, 2009, 11:22 am
Thank you for the reply. I did the same except the last part, “then use Columns to Text to do the actual conversion” as you mentioned, but it didn’t work. Macro didn’t write anything in VBA and it is not executing the operation when I run the recorded macro. However I don’t understand the last part that you mentioned. What do you mean by use columns to text to get final conversion? I don’t get anything like that.Your suggestion will be appreciated.
For the chart what I want to do is that after making the bar chart I want to lebel the chart automatically using macro e.g., change the grid lines, lebel x & y-coordinate (plant vs yr) ans so on. I tried but I am unable to do that. Can you please suggest?
Thank you again for your help.
Comment from Jon Peltier
Time: Wednesday, December 9, 2009, 12:17 pm
You did everything except text to columns? Text to Columns is the part that does the conversion.
About the chart, if you’re using Excel 2007 to record your macro, you won’t get much useful code. Microsoft rushed 2007 out the door before they had time to make everything work right. You can either find an earlier version of Excel for recording macros, or wait for Excel 2010 to come out in a half year.
Comment from Razib
Time: Thursday, December 10, 2009, 12:41 am
Thank you for your suggestion. I am able to modify the charts using macro in 2003 but not 2007.
I am sorry to say that for the first problem to convert string to number, I am unable to find any command to convert “column to text”. I am explaining the fact again. I have selected the cells and click on the command “convert to number”. Then it converts the text to number. Then stop recording the macro. Now if I run the macro, it only selects those cells but does not convert the texts to numbers. However, as you mentioned that I have to select “column to text”; so far I am unable to find that command from excel. I am afraid that it might cause some errors. Can you please tell me that from where can I get that command. I am using excel 2007.
Comment from Jon Peltier
Time: Thursday, December 10, 2009, 8:29 am
Razib -
Data tab > Data Tools group (4th from left) > Text to Columns (first item in group).
I recorded a macro, cleaned it up, and here is the function that converts a range of text values to their numeric equivalents:
Function ConvertTextToNumbers(rng As Range)
Dim col As Range
For Each col In rng.Columns
col.TextToColumns Destination:=col, DataType:=xlDelimited
Next
End Function
This is how the function is called:
Sub TestTextToNumbers() ConvertTextToNumbers Selection End Sub
Comment from Razib
Time: Wednesday, January 20, 2010, 11:54 pm
I want to generate an order using conditional randbetween function in excel 2007. For example, I want to generate 2000 orders in between 1 to 730 (randbetween(1,730)). But each number betweeen 1 to 730 can’t be repeated more than three times withinin these 2000 orders. Is it possible?
How can I write this function in excel?
Comment from Jon Peltier
Time: Thursday, January 21, 2010, 12:11 am
Razib -
There are more effective and relevant places to ask for help. I suggest you read Finding Help for Microsoft Excel.
Comment from Shubha
Time: Saturday, July 31, 2010, 5:36 pm
Great post ! Thank you. Helped me a lot
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.