The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

How to make custom Excel macro/button available to all workbooks

I've set up a button that runs an Excel macro.  However, I can't figure out how to make it available to all of my Excel documents.

In Word, any custom buttons become part of the Normal template, but there doesn't seem to be an equivalent in Excel.  I looked around in MSDN but couldn't find the information I need.
Blue State Bitch Send private email
Monday, April 04, 2005
Make a custom toolbar button?
Chris Altmann Send private email
Monday, April 04, 2005
And you can put the templates containing macros (and more buttons) in XLStart:
Chris Altmann Send private email
Monday, April 04, 2005
Hi Blue,

Excel does not have a concept like the template in Word. For example, documents created from Word templates retain an 'attachment' to the source template, so if you make paragraph style or macro changes to a document, Word offers to save those changes to the source template. That does not happen in Excel.

If you have VBA code that you want to have available to all documents you have two main approaches: the personal.xls workbook or an xla Add-In (I'll stay away from COM add-ins for now - let's not complicate the issue).

The personal.xls workbook is effectively a hidden workbook which gets loaded each time Excel starts (if the personal.xls workbook actually exists). It is typically saved in the XLSTART folder. If it doesn’t exist yet the quickest way to create it in the correct folder is to record a quick macro. When the record macro dialog appears, from the “Store Macro in” drop-down select “Personal Macro Workbook”.

To have a macro available to all workbooks you’ll need to write code to create a menu item or toolbar button which runs the code. The menu item or toolbar button should be created when personal.xls opens either using the Workbook open event (my preference) or a macro called Auto_Open.

The other approach is to create an xla add-in which effectively works on the same principle as the personal.xls file to the extent that you write code for the add-in’s open event to create menu items (or even a whole new menu) and/or a toolbar. You add-in can include userforms, class modules, code modules – the whole shebang.

If the macro is for your own personal use then go down the personal.xls path – it’s the quickest and easiest. If you have macros that you want to distribute to many people in your organisation use an xla. You can save it so a central location on the file server and reference the xla from each person’s copy of Excel (it takes a single registry entry – you can even write a macro that automates the process which you email to the intended users). In this way, when ever you have an update to the add-in you copy it to a single location (on the network) and the users simply re-start Excel to receive the update. (When Excel starts it takes a copy of the xla and loads it into memory) The xla approach gives a lot of flexibility when it comes to distribution.

Good luck
Regard - Marcus from Melbourne
Marcus from Melbourne
Monday, April 04, 2005
Thanks to all who replied to this topic.  I'm pursuing the Personal.xls solution to this problem.
Blue State Bitch Send private email
Monday, April 11, 2005

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz