How To: Assign a Macro to a Button or Shape
by Jon Peltier
Wednesday, March 12th, 2008
Peltier Technical Services, Inc., Copyright © 2012.
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
- How To: Use Someone Else’s Macro
- Forms Controls and ActiveX Controls in Excel
- How To: Fix a Recorded Macro
Posted: Wednesday, March 12th, 2008 under VBA.
Comments: 53
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
Comment from Anup
Time: Monday, September 6, 2010, 3:36 am
G8 …. superb … Thanx a lot …..
Comment from Alan Horsfield
Time: Saturday, January 15, 2011, 4:39 am
I am using Excel 2003 and only a customised toolbar I call ledger. It contains a few of the most useful standard buttons and 10 of my own for specific actions on a ledger account. The special 10 buttons are all assigned to macros which can also be operated using a control command. Everything works fine BUT, if I move the file to another folder and in particular to a CDrom, the buttons no longer work until they are reassigned.
Is there an answer to this problem?
Comment from Jon Peltier
Time: Saturday, January 15, 2011, 9:31 am
Alan – Do all the buttons no longer work, or just the custom ones?
Comment from Alan Horsfield
Time: Saturday, January 15, 2011, 12:44 pm
Thank you Jon for your interest. Yes, all the standard buttons in my “ledger” toolbar work but the custom ones do not. However, the commands still work via the control keys.
Comment from Jon Peltier
Time: Sunday, January 16, 2011, 8:38 am
Depending on how you’ve set up those buttons, they might be linked to the whole path of the workbook that contains the macro. Move the file, the workbook doesn’t exist at that path, nothing happens.
A long time ago I stopped using attached toolbars for this reason. I build the toolbar when the workbook opens, and destroy it when the workbook closes. I have some very rudimentary code showing this technique in this text file:
http://peltiertech.com/Excel/DummyMenuBar.txt
Where it says for example
.OnAction = “Macro1″
I usually change to this:
.OnAction = ThisWorkbook.Name & “!Macro1″
to make sure the macro points to the correct workbook.
Comment from Alan Horsfield
Time: Monday, January 17, 2011, 9:39 am
Thank you again, Jon. I have adapted some code similar to that in your MenuBar.txt and it works well but it deletes my carefully crafted buttons and replaces them with various buttons from the msoControlButtons set which I do not want.
I happen to have a page of instructions in my Excel file with a list of cells containing the buttons. I can assign macros to them and they remain operational if I move the file to another folder. Is there a way of giving these buttons an “id” that can be used in place of “.FaceId” and, instead of using “Type:=msoControlButton”, use some other “Type:=”? These buttons have a name like “Picture6_Click” associated with them but I do not know what it means.
Incidentally, I have an Excel2007 version of my file called ledger.xlsm with a number of subs like:
Callback for customButton1 onAction
Sub Macro1(control As IRibbonControl)
Run (“Check_R”)
End Sub
These enable me to replace the standard ribbon with my own containing my custom buttons plus useful standard ones, and the custom ribbon transports with the file. If I unzip the .xlsm file, there is a whole lot more information there including the .png files of the buttons.
So am I asking too much of Excel2003!
Comment from Jon Peltier
Time: Monday, January 17, 2011, 10:11 am
Alan -
Since the button images reside as pictures in your sheet, copy them, then use .PasteFace to paste the image as the button image.
Comment from Alan Horsfield
Time: Tuesday, January 18, 2011, 12:56 pm
Jon, Thanks to your suggestion to use .PasteFace everything is now
working nicely when copied to a CDrom, so I can give my file to a
friend without having to set up the assignments for him.
When I had all the code in ThisWorkbook, it did not like:
Set myControl = CommandBars(“Ledger”).Controls.Add
So I put the code for making the toolbar in a module as a subroutine
which included the same piece of code and called it up in
ThisWorkbook. It works nicely.
Many thanks again.
Alan
Comment from Anonymous
Time: Tuesday, June 7, 2011, 11:54 am
Great info…..saved me a lot of time trying to go through Excel’s help menu and books. Thanks!!
Comment from Chris
Time: Friday, August 19, 2011, 4:01 am
Hello Jon
I have written a macro in the sheet module. The macro has no parameters.
When I try to assign macro to my shape the macro does not appear in the macro list. If I have a macro in a general module I see it. In this particular case I want to pick up a value from the sheet that is calling the macro which is why I put it in the sheet code area.
Any ideas, please?
Comment from aaron edwards
Time: Friday, September 2, 2011, 9:17 am
I often create simple drawings in Excel for the machhine shop to make “quickie” fixtures and illustarte ideas. In one drawing I wanted to animate the movment of a blade shearing off the tops of the balls in Ball Grid Array device to see how many voids were inside after solder reflow. I started a macro and recorded the object as I moved it first to the left and then the right. It looked awsome. I thought I had figured out away to animate my drawing!
No such luck. Even though the VBA code seemed to recorded all by keystrokes, the macro failed saying OBJECT DOES NOT SUPPORT THIS PROPERTY OR METHOD. RUN TIME ERROR 438.
Is there a way to do simple animation of drawing objects in Excel by using a macro?
Comment from Jon Peltier
Time: Friday, September 2, 2011, 10:07 am
Aaron -
Each drawing object (shape or group) has a .Left and .Top property, which you can vary within a timed VBA procedure.
I’ve covered animation with VBA in these articles:
Gapminder for Excel
Gapminder for Excel II
Gas Prices – Animated Bar Chart for Excel
Gas Prices – Animated Bar Chart for Excel 2
Ballistics Animation
Rolling Wheel Animation
Comment from aaron edwards
Time: Friday, September 2, 2011, 1:59 pm
Thanks Ron for the Cool Chart and data related links that animate the ballistic chart. I’ll surely use these ideas in future applications. But the problem of animating an actual drawn object across the crenn is slighly different. I managed to solve that problem by alternating the rmacro ecorded keystrokes to move the object and then selecting an empty cell, back and forth repeatedly. This worked perfectly when I ran the macro afterward. Once I saw the VBA code, I simply copied the number of intrations required for the full movements I desired.
Simple but effective.
Comment from meking
Time: Thursday, September 15, 2011, 12:15 pm
I want to change the button image like we could in excel 2003. Looks like we can no longer do this, so all my macro buttons on my ribbon look the same and the only way to know which one is which is to float mouse over it. Or is there a way to draw your own image as before?
Comment from Jon Peltier
Time: Thursday, September 15, 2011, 1:15 pm
Meking -
You can add a button to the QAT, and while in the “Customize the QAT” dialog, select the new item, then click the Modify button, and you can choose from many button images.
If you want to use your own button image, you need to use code.
Comment from paulchiu
Time: Tuesday, January 3, 2012, 11:08 am
mysteriously, a button to execute a macro inside an 2010 EXCEL workbook disappeared and the “insert” feature within the “Developer” tab is disabled.
Any ideas to get this back for this workbook? The “insert” works for other workbooks.
Thanks!
Paul
Comment from Jon Peltier
Time: Tuesday, January 3, 2012, 12:00 pm
Paul -
Is the workbook or worksheet protected? Are multiple sheets selected? Is the workbook shared?
Comment from paulchiu
Time: Tuesday, January 3, 2012, 12:13 pm
Jon,
The worksheet is on my local drive and only the sheet where button was located before it disappeared is selected.
Not sure about the worksheet being protected.
Thanks,
Paul
Comment from Jon Peltier
Time: Tuesday, January 3, 2012, 12:27 pm
Towards the right of the Home tab, click Format, and look for Protect Sheet or Unprotect Sheet
Comment from paulchiu
Time: Tuesday, January 3, 2012, 12:40 pm
I selected all the sheets and the Protect Sheet selection is faded out.
Comment from Jon Peltier
Time: Tuesday, January 3, 2012, 11:56 pm
If all sheets are selected, you cannot change the protection of the sheets.
Comment from paulchiu
Time: Thursday, January 5, 2012, 12:00 pm
i gave up.
i am running the macros manually.
Comment from Mark T
Time: Wednesday, February 1, 2012, 5:47 pm
Hi, Jon. I haven’t had much luck finding an answer to this question; maybe you can help.
I currently have a button set up in an Excel 2007 document (let’s call this Spreadsheet A) to execute a macro on our company network (stored in Spreadsheet B). In the “Macro Name” field, the full path of Spreadsheet B is defined alongside the Subroutine. The macro works fine when I leave Spreadsheet A in its original location. The problem arises when I copy & paste Spreadsheet A elsewhere… when I do this, Microsoft automatically resets the path of Spreadsheet B to match the new location of Spreadsheet A.
I do not want to do this. I just want to leave Spreadsheet B alone, and preserve the path within Spreadsheet A. (Interestingly, this path name is preserved when I do a “Save As”, but not when I copy & paste in Windows Explorer).
Do you have any suggestions as to how I might fix this problem? Is there a way to “lock” the path name in place?
Comment from Jon Peltier
Time: Wednesday, February 1, 2012, 8:32 pm
Mark -
Save the path and sub name in a cell or in a constant in your module. When the button is clicked, have it run this code:
Application.Run(strMacro)
strMacro is either the constant, or a string variable defined by:
strMacro = activesheet.range(cell address containing path/sub).value
Comment from Mark T
Time: Thursday, February 2, 2012, 3:44 pm
OK, I just tried that… unfortunately, I got an error message telling me that “the macro may not be available in this Workbook, or all macros have been disabled”.
I don’t think this is a macro security issue, as this will work when accessed directly from the “Macro Name” text in the button. Are you still supposed to use an exclamation point between the path name and the macro name?
Comment from Jon Peltier
Time: Friday, February 3, 2012, 1:33 pm
Mark -
I think when you use Application.Run, the workbook with the procedure in it has to be open.
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.