Making macros available on lan in Excel 2000

[Ladislav Ligart]

I know this has been discussed before but I didn't get a
sufficient/complete answer reading past posts.

I have a workbook containing macros I want the entire office to be
able to use. It needs to go on the lan in one place only--no
distributing to workstations.

If I put the workbook as an .xls file on the lan, and have the users
set their "alternate startup file location" to that path, the macros
are available, but the 1st user who opens Excel it gets it writable,
and the 2nd-on users get a "this workbook is in use by so-and-so, do
you want to open read-only / notify / cancel" message, which is
unacceptable.

So, I tried saving it as an .xla file in the same directory. Now,
users get the workbook loaded as an add-in (I can see it in the VBA
editor), but they can't see the macros from Alt-F8, meaning for all
intents and purposes they don't have access to them.

I've read about people using the XLStart directory, but that exists on
each workstation in the user's profile directory, which means
distributing my workbook, which I don't want to deal with.

Is there a way to do what I want? Best I can figure at present is that
I stick with the add-in solution, and include a new menu button w/ a
drop down list populated with the macros in my add-in workbook?

L.L.

 

[Gord Dibben]

Ladislav

Add-in macros are not listed when you hit ALT + F8 as you have found out.

They are still available for users, just need an alternate method of accessing
and activating them.

Users can add buttons or menu items with the macros assigned to the buttons or
menu items.

You can write code in your Add-in that will create a Toolbar or new Menu when
it opens. This might be the best method.

For more on creating Toolbars and Menus.........

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dno97ta/html/ofcmdbar.asp

http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q166755&

http://support.microsoft.com/support/kb/articles/Q159/6/19.asp

Gord Dibben Excel MVP

 

[Guest]

Try using .xlt extension. I hope that will serve the purpose.