best way to set up macro library so all spreadsheets have access?

[RSS]

Hi,

I have lots of different macros recorded in different spreadsheets. Is
there a way to create a macro library so all of my spreadsheets would have
the same access to all of my macros?

It would br great if I could group macros by subject content e.g.
statistical macros, logic macros, movement macros, etc and then be able to
load the groups that I need for a given spreadsheet.

I'm just not sure how to set this up. I have a basic macro recorded and it
seems like when I do these they become part of the current spreadsheet. The
only way I can reuse the macro is to copy it into the new spreadsheet or I
think, have the old spreadsheet opened in the background.

Does this make any sense? There's got to be a better way

 

[Jay]

Hi RSS -

Here is a microsoft link that describes the process of using the special
workbook 'Personal.xls' to make your macros universally available in excel:

http://office.microsoft.com/en-us/excel/HA010872961033.aspx

This is generally a good way to make all of your custom macros available,
but you could also turn them into 'add-ins.' Add-ins might provide improved
grouping capabilities through a menu structure, but the setup and maintenance
effort is much greater. With the Personal.xls approach, any macros added (or
changed) in personal.xls are instantly available and you can manage them all
in a single file.

 

[Alan]

You could place all your macros in one workbook, in the ThisWorkbook module.
Use an Autpen event to Build a toolbar. Sort the macros in groups and
structure the toolbar with a group name on a CommandBarPopup, and a button
in the popup for each macro. Include an install Msgbox asking to install the
toolbar. If Yes, then install the toolbar. Building the toolbar in this way
will automatically link the toolbar to the macros in the workbook, no matter
where it is stored, local or network. If you are interested, I can post the
toolbar structure.