Share a custom macro toolbar

[Guest]

I have created a custom macro toolbar that we want to deploy to about 3 to 4
users. I have already updated everyone's Personal macro workbook with the
updated macros. How can I put the updated macro toolbar in each user's Excel,
without overwriting all of their other settings? I know that all of the
settings for Excel are stored in an "xlb" file and I could replace their file
with mine, but then they would lose all of their other settings. Any
suggestions?

 

[Guest]

You could attach the toolbar to each person's Personal macro workbook (via
View, Toolbars, Customize, Attach in the first tab). However, you would need
to open each of their peronal.xls files on a machine that has the toolbar
already installed which could be awkward.

Wouldn't it be easier all around to put all the macros you want them to have
onto a new workbook and then save it as an add-in (with the toolbar attached
to it as described above)? This .xla file could then be saved once to a
shared folder and then that folder specified as the startup location for
everyone's Excel: Tools, Options, General tab (this can be done via GPO if
you're using Active Directory). This approach has the advantage of being
centralised and avoiding messing with everyone's individual personal.xls.

 

[Dave Peterson]

I agree with Martin.

Create a new workbook, move all your macros into that workbook and save it as an
addin.

Provide some sort of interface to the user so that they can run the macro.

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)

After you distribute the addin, you can tell the users to use:
tools|addins|
to browse for it and turn it on.

In fact, if all the users have access to a common network drive, you could put
the addin in a folder on that drive and tell them to browse for that addin
(tools|addins). Then you don't have to distribute the file to any person.

And don't name it personal.xla. Only one workbook with that name can be open.
And if the user has their own personal.xla, they'll have to make a decision on
which one to use.

JeffbertUtils.xla seems like a nice name!

 

[Guest]

I just did the same thing you are looking to do, save the macros in the
workbook and use the add-in, just name it the same name as your workbook.
When other users install the add-in it just copies it to their computer.