Distributing A Custom Menu

[Fester Bestertester]

Excel 2003, Win XP:

Okay, I'll admit, I'm something of a newbie at Excel programming.

I've created a custom menu which runs a series of macros. Can I
distribute these to other users within my network by saving them out to
a shared directory? How, in general, would I go about distributing a
custom menu and the macros that the menu is supposed to run?

 

[JW]

Build them as an add-in.
http://www.fontstuff.com/vba/vbatut03.htm

 

[Dave Peterson]

I'd save the file as an addin (*.xla).

Then either place that file in a common network share (easier for updating) and
have the users use Tools|Addins to install it (or just tell them to open it when
they need it).

But you could email it to them and tell them to store it on their local drive
(useful if some users are not always connected to the network).

If you have UDFs called from worksheet cells, I'd tell them to put the file in a
specific folder on their C: drive (C:\FesterUtils\addinnamehere.xla). Then they
could install via tools|addins or just double click when they need it (same as
before).

It'll make sharing workbooks easier. You won't have to answer questions why the
UDFs can't be found and how to change the links so that the point to the correct
location.

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)

 

[Fester Bestertester]

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

Thanks, this one actually works really nicely. I edited the file so that
it has the macros I want, then edited the menu creation data sheet to
fit my macros. I saved the .xls, then also saved it as a .xla, to a
shared directory. Then I went around and pointed everybody's Excel
application to that shared .xla (not the duplicate one it wants to place
on the user's local Addins folder).

When I need to make changes, I edit the .xls and save it, then kick
everybody off Excel just long enough to also save it as the .xla. The
result is that the next time anybody opens Excel, the changes are available.

Pretty nice!

 

[Dave Peterson]

Just wondering why you saved is as a .xls file at all.

Maybe to make the saving easier?

 

[Fester Bestertester]

Just wondering why you saved is as a .xls file at all.

Maybe to make the saving easier?

When I first downloaded and opened the menumaker.exe, it produced a .xls
file with the MenuSheet worksheet, which allows me to edit the menu.
Then, after making all my menu edits and adding all my macros to the
code, I also saved it as a .xla.

When I opened the .xla, I discovered that, because it's an add-in file
(?), the menusheet worksheet was not available. So it appears that if I
want to modify the menu, I have to do it via the worksheet in the .xls.

This suits me fine anyway, since I have users who run Excel all day
long, and the .xla addin file is on a shared directory. So with this
approach, I can edit the menu and macros whenever I want, and I only
need to kick the users off of Excel for the one minute or so that it
takes to copy it over as an .xla again.

 

[Dave Peterson]

You can toggle the workbook from addin to not-addin via:

Inside the VBE
select the ThisWorkbook module of your project
Hit F4 to see the properties
Look for IsAddin
and toggle true/false to what you need.

 

[Fester Bestertester]

You can toggle the workbook from addin to not-addin via:

Inside the VBE
select the ThisWorkbook module of your project
Hit F4 to see the properties
Look for IsAddin
and toggle true/false to what you need.

Ahh, so, very cool. Just goes to show...I've written lots of Access VB
and lots of VB and ASP.NET, but Excel programming is still a different
planet for me...maybe I should get a book while I can still get books
for Office 2K3. I hear 2K7 is all xml based, so it's a whole different
ball of wax and you can't really distribute a menu this way any more...

 

[Dave Peterson]

You can still create a toolbar, but it'll show up in the Addins section.

If you really want to do stuff in xl2007, there are 3rd party addins that you
may like.

One is Patrick Schmid's ribbon customizer:
http://pschmid.net/index.php

(or directly)
http://pschmid.net/office2007/ribboncustomizer/download.php

From the documentation page:
http://pschmid.net/office2007/ribboncustomizer/featuretourpart3.php

====
And Ron de Bruin has lots of stuff here:
http://www.rondebruin.nl/tips.htm

(I've done just enough in xl2007 to be scared <vbg>.)