First, does your template contain macros or are the macros really in your
personal workbook? (I'm kind of confused(!).)
As a general rule, I like to keep my data/formatting (stuff in template files)
separate from the macros that workbooks based on the template uses.
I think it makes it much easier. If the code is in the template file, then each
new workbook based on that template now has its own version (identical copy) of
all the code.
When (not if!), that code needs to be changed -- fixed or enhanced -- you'll
never be able to track down all the workbooks that have them. The template file
can spawn lots of workbooks and each workbook can spawn lots and lots more.
And even if you could find all those workbooks, it's a miserable job to update
the macros in each.
And you don't want to share your personal workbook with others (but you do want
to share the relevant macros). If you tell me I have to use your personal.xls
(say) workbook and I already have one, I'm not gonna be happy to turn mine off
to use yours.
Instead...
I'd still create the template file (.xlt), but also create an addin that
contains all the macros. Then I'd tell the users that whenever they open a
workbook that needs the macros (either an existing workbook based on that
template or a new workbook based on that template), then they have to open the
addin, too.
This makes updates a lot easier (well, somewhat easier). If a macro changes,
you fix your master copy of the addin and plop it back onto the common network
drive where the users find the addin.
(More later if you have to share individual files with each user (if they don't
have access to the common network share).)
But (there's always a but!)...
The users need a way to access those macros. The macros don't show up in the
tools|macro|macros dialog. So you need a nice way for them to run them.
For additions to the worksheet menu bar, I really like the way John Walkenbach
does it in his menumaker workbook:
http://j-walk.com/ss/excel/tips/tip53.htm
Here's how I do it when I want a toolbar:
http://www.contextures.com/xlToolbar02.html
(from Debra Dalgleish's site)
In xl2007, those toolbars and menu modifications will show up under the addins.
And if you use xl2007:
If you want to learn about modifying the ribbon, you can start at Ron de Bruin's
site:
http://www.rondebruin.nl/ribbon.htm
http://www.rondebruin.nl/qat.htm -- For macros for all workbooks (saved as an
addin)
or
http://www.rondebruin.nl/2007addin.htm
And Bob Phillips shows a way to use a wrapper so that it can work in both xl2003
and xl2007.
http://msmvps.com/blogs/xldynamic/archive/2010/03/27/deploy-me-simple.aspx
=========================
More about if you have to share those individual macro workbooks with users.
If your macro workbook (addin) contains any UDFs (userdefined functions) located
in cells in a worksheet, then if the users put that addin in any path that they
want, you're going to be hit with a lot of phone calls--especially when your
users share workbooks with each other.
All my functions that used any of the UDFs turn to NAME errors and I get asked
to point to a file to update links. What's going on???
Those UDFs will point to the drive/folder/filename that was used by the original
developer of the formula (not you as the developer of the addin -- well, maybe
you as the developer of the Template).
Instead of saying put this addin on your local harddrive, tell them explicitly
to create a new folder on their C: drive. Call it ExcelAddins.
And file the addin in that folder as:
C:\exceladdins\CBUtils.xla
(or xlam)
Since everyone will be using the same location and same name, excel will not
have any trouble with links or NAME errors.
========
So if you listen to any of this, you've got some work to do.
You have to create a macro free version of your template file.
You have to create an addin that contains the macros from the template file (and
your personal workbook!)
And you have to add some housekeeping code to that macro workbook that allows
the users access to the macros.
This may seem like a lot of work, but I wish I had done this kind of thing for a
few of my early projects.
But you live and you learn, right?
