How do you add a global Excel Macro?

dfins used Ask the Experts™
I'm hoping this is just a simple answer, I don't know much about Excel macros, but couldn't find exactly what I was looking for searching on EE.  When I add a macro button such as the pic below.

 Adding custom macro
The button is added globally in my excel.  But if I create a new excel document, click my new facy macro button, it opens up the original excel document and executes on that.  So what I'm asking is how do I make a global macro in Excel 2007?  Thanks!
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2008

Save the macro in your personal macro workbook first. Then add your fancy button.

Top Expert 2008
Easiest way to create a personal macro workbook is to record a macro and choose personal macro workbook in the Save in option. Check out tutorial for more details:

Most Valuable Expert 2011
Awarded 2010
Hello dfins,

you can store macros that you want to be available in every workbook in a workbook called Personal.xlsb. Save this file in the Excel Startup folder, so it gets opened every time you start Excel. You can hide the workbook, so it does not show in the list of open files.

Now create your macros and store them in Personal.xlsb, making sure that they work independent of a specific file, for example, do not refer to a specific workbook but rather the active workbook.

If you do not want to use a Personal.xlsb, you can save your macros as a XLAM file, which you can then add to Excel via the Add-in dialogs.

Does that help any?

cheers, teylyn
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Expanding on teylyn's Comment, could you post your macro in the code snippet? We can help you revise it so it works anywhere.

You also stored your macro in the code pane for the worksheet. It would probably be better to store it in a regular module sheet (in the VBA Editor, use Insert...Module to create one). If you want to run it using a command button, pick one from the Forms toolbar rather than Controls ToolBox.

As the others have said, if you store the macro in your PERSONAL.xlsb workbook, it will be available (opened automatically) whenever you want launch Excel. If you don't put it there, then you will need to open the workbook that contains your macro before it will run. That's why Excel opens the original workbook whenever you click on the button with a new workbook being active.



Increasing point value because the correct answer came in at exactly the same time, going to split the points from there.  Thanks everyone.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial