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.
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.
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.
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.
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!
No, not to your MAC’s dock. I’m talking about dragging it to the Excel toolbar at the top of your computer window.
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?
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.
Perfect – that helped, thanks..
Paul A says
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?
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.
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?
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.