Add Macro Button to the Toolbar in Excel 2011

2012-07-07

You can add an icon to the toolbar in Excel 2011 for your Personal Workbook Macro. In an earlier post I created a short macro to imitate the Control+Home keyboard shortcut in Excel for Windows. You can add an icon to the toolbar to run that, or any other macro with a few quick steps.

  • Right click on the toolbar and select Customize Toolbars and Menus… then

  • Click the Commands tab, then

  • Scroll down and select Macros from the Categories pane, and

  • Drag the Custom Button with a smiley face to the toolbar, then

  • Click OK to get rid of the dialog box.

Next you:- Right click on the smiley face, select Assign Macro… and

  • Pick the macro you created (in your personal macro workbook), then

  • Click OK.

Change the Smiley Face Image

If you don't like the smiley face icon staring back at you want to change it (like I did) that's easily done as well.

  • Right click on the Custom Button icon and select Properties... then

  • Type a name in the Name: box, then

  • Click the drop-down arrow beside the smiley face and select from the icon list, then

  • Click OK.

Use a Custom Image for the Toolbar Button

The icon list is rather short and pathetic, but you need not restrict your choices as Excel allows you to copy and paste an image into the button face. Here is how I changed my smiley face image.

  • Choose Insert > Symbol

  • Click Shapes (at the top of the dialog box)

  • Right click on your favorite shape and click Copy, then

Copy Symbol Image

  • Close the Media dialog box, and

  • Right click on the smiley face toolbar icon, then

  • Click Properties...

  • Click the drop-down beside the image, and

  • Click the option Paste Button Image, then

  • Click OK.

Move Your New Macro Button

In case you didn't get your button located in the right place on your toolbar when we first started this exercise, the button is easily moved.

  • Right click on the Toolbar and select Customize Toolbars and Menus...

  • Drag your icon to a position of your choice, then

  • Click OK.

Archived comments

Comments migrated from the previous version of the site. Adding new comments is disabled.

Emilie19 sierpnia 2012 07:42
When you write "Custom Button with a smiley face to the toolbar, then", do you mean the dock at MAC? It dosen't work when I try to drag the Custom Button with a smiley face to the dock, and I really like to do that!
Gregoryexcelsemipro.com19 sierpnia 2012 14:48
No, not to your MAC's dock. I'm talking about dragging it to the Excel toolbar at the top of your computer window.
Emilie19 sierpnia 2012 15:33
Okay, I have misunderstood, but maybe you can help me. The thing is that I just started using Excel 2011, instead of a older version. When I open Excel, I first have to choose which Templates, I want to use, before it opens the spreadsheet. I just want to start in the normal spreadsheet, instead of have to choose first, do you know how to fixe that?
Gregoryexcelsemipro.com19 sierpnia 2012 16:50
Open Excel, and after you reach the spreadsheet, choose Excel > Preferences... In the Excel Preferences dialog box click General, and then uncheck the box for: Open Excel Workbook Gallery when application opens. Then click OK. Problem fixed.
Emilie19 sierpnia 2012 19:57
Perfect - that helped, thanks..
Paul A16 września 2012 11:03
When I assign a macro to the custom button it works great. But then if I copy the excel workbook in Finder to a new name and open this, the custom button references the macro in the original workbook. Is there any way around this? Thanks, Paul
Gregoryexcelsemipro.com16 września 2012 23:57
Yes. Your macro has to be located in your Personal Macro Workbook. When you click the Record button for a macro, the Record Macro dialog box comes up. Under Store macro in: click the drop-down and select Personal Macro Workbook. Now the macro will not have to reference another file.
AJ18 marca 2013 17:47
If the Marco is saved in "Peronal Macro Workbook", it will work only on the same PC. I have the file on my Flash Drive and use different computers, how can we make sure, It would work on any computer? I would work even if the file name is changed. This is what I would like to do: Create the default excel file with all the buttons I want on all my sheets. So whenever I create a new file, all these button will already be in there. And ofcourse I will have to save the file with it's as new name. Is it possible to make this work?
Gregoryexcelsemipro.com31 marca 2013 15:54
What you are describing, create an Excel file that has all these macros and buttons, then when you switch computers the macros and buttons will still be there is possible if you create the macros and buttons in a specific file. That file will carry those macros and buttons with it wherever you access it. The second part, creating a new file and having these buttons would have to be tied to a specific Excel program on a specific machine. I don't know how to make that work. Sorry.