How could I share a custom toolbar on the newtwok for all users?

[Eric]

I need to share a custom toolbar on the network so that when multiple users
use a common terminal with Excel, the custom toolbar will appear. I would
prefer having a single custom toolbar file so that there is only one file to
update from time to time and to make sure that everyone's toolbar is
identical.

I have Excel 2003. I believe something has to be done with the registry but
what I tried so far using instructions from Excel 97 hasn't worked so I'm out
of options.

 

[Dave Peterson]

Saved from a previous post from someone named Formido:

Don't use a workbook named personal.xls (or personal.xla). If you share this
same workbook with others, then those users may have to decide to use your
version of personal.xl* or their own.

Call it something nice:
FormidoUtils.xls (or .xla)

I'd save it as an addin (*.xla) and store a single copy in a nice common network
location and refer to that location via its UNC path
(\\aaaa\bbbb\formidoutils.xla). I wouldn't use a mapped drive--others may not
connect using the same letter.

If that's not possible, I'd ask all the users to store the addin in a folder on
their C: drive--and make that folder name exactly the same for all users:

c:\Formido\formidoUtils.xla

Then any UDF used in a cell will always find the function in the same
location--it'll make working with links and users easier.

And if you have subroutines that need to be run from that addin, you'll have to
give the user a way 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)

 

[Gord Dibben]

Create the Toolbar through VBA code and make it available to all.

Steps...............

Open a new file and add the code for creating a Toolbar. See below for help.

Save the file as an add-in.

Place the add-in in a common folder and instruct all users to load that add-in
and keep it loaded.

For help on creating a Toolbar see Debra Dalgleish's site.

http://www.contextures.on.ca/xlToolbar02.html


Gord Dibben MS Excel MVP

 

[Eric]

Thank you for your replys,

I tried the instructions that were given but it only shows how to do it with
a macro button. How do I write the VBA code for a custom toolbar that is not
floating in addition to a macro button that is on the toolbar with regular
buttons?

 

[Dave Peterson]

I don't understand what you mean by a macro button.

The code at Debra's site creates a toolbar with icons that can be clicked to run
the macros.

If you don't like it floating...

You can change this portion:

.Position = msoBarFloating
to
.Position = msoBarTop
or
.Position = msoBarBottom

Personally, I like to float it in the window. The user can move it to where
they want, but I figure that they'll notice that it was added!

 

[Eric]

Thanks for clearing up how to fix the floating toolbar. What do I do If I
just want to share a custom toolbar with no macro at all?

Also, If I ever updated this add-in, would it be automatically changed in
excel or would the users need to remove it and reinstall the add-in? If
that's so I'd rather look for another way of sharing the toolbar.

Is there a way that you can go into Excel's registry to change it's path to
the toolbar file? That to me sounds like it would be the easiest way.

 

[Dave Peterson]

#1. You could add built in commands to the toolbar if you wanted.

#2. There is code in the workbook that builds the toolbar each time the
workbook/addin is opened. And that same toolbar is destroyed when the
workbook/addin is closed.

So if you update the file and distribute it to your users--and they install it
in the correct (same) location, then there should be no problems.

#3. I've seen a KB article that describes how to use a network drive to hold
the toolbar file--instead of a local (probably C drive.

If you're creating a toolbar with a bunch of icons that you like -- and you can
destroy the user's toolbars, then you could just search for your excel11.xlb
(xl2003 toolbar file) and share it with them. Have them put it in the correct
location and their customized changes will be replaced with yours.

If your users don't care (and lots don't!), then this seems like a reasonable
approach--well, I've done it for others. But if your users care, I wouldn't do
it.

 

[Eric]

Method #3 is the way I've been trying to go, but I haven't found a way yet to
link all of the users Excel files to automatically link to a common Excel11
file on a shared network drive.

Of course I can always manually place my custom Excel11 file in their
folders on the C: drive, but that would defeat the purpose of having one
toolbar file to maintain and update as time goes on and new users are added.

Do you know how I can find this "KB" article you referenced? It sounds like
this would be my best bet.

As I mentioned before, the best thing I could find was an old Excel 97
Microsoft help that told me to go into the registry to link to the toolbar
file, but unfortunately it didn't work.

 

[Dave Peterson]

This refers to xl97:
http://support.microsoft.com/kb/161235/en-us
XL97: How to Use a Toolbar Settings File from a Network Share

Method #3 is the way I've been trying to go, but I haven't found a way yet to
link all of the users Excel files to automatically link to a common Excel11
file on a shared network drive.

Of course I can always manually place my custom Excel11 file in their
folders on the C: drive, but that would defeat the purpose of having one
toolbar file to maintain and update as time goes on and new users are added.

Do you know how I can find this "KB" article you referenced? It sounds like
this would be my best bet.

As I mentioned before, the best thing I could find was an old Excel 97
Microsoft help that told me to go into the registry to link to the toolbar
file, but unfortunately it didn't work.

 

[Eric]

This is the document that I have already tried and for some reason I can't
get it to work right. The only thing I can think of is because it is so old
that perhaps it does not work the same anymore.

 

[Dave Peterson]

Could be.

I don't have a network to test.

This is the document that I have already tried and for some reason I can't
get it to work right. The only thing I can think of is because it is so old
that perhaps it does not work the same anymore.