can you store macros on server?

[Daniel M]

I have a few macros i am writing for mulitple users to access in multiple
spreadsheets that may get updated on occation. Is there a way to store them
on a server and have everyone pointed to it? I dont really want to have to go
update everyones machine with a new personal.xls file each time. Any other
suggestions?

Thanks.

 

[Dave Peterson]

Sure.

In fact, you may want to save your macros in an addin. Then the users could use
tools|addins and browse to that location.

And you may want to give the user a way to execute the macros:

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)

If you want to learn about modifying the ribbon in xl2007, you can start at Ron
de Bruin's site:
http://www.rondebruin.nl
or
http://www.rondebruin.nl/ribbon.htm

 

[Daniel M]

OK i understand i can save a excel file with a custom menubar in it. in that
menubar i can call macros. but the macros themselves do not show up when i do
tools...macros. Is there a way for this to happen?

THanks.

daniel.

 

[Dave Peterson]

If you save the workbook as an addin, then the macros won't appear in that
dialog.

You can either give the user a way to access the macros (toolbar???). If I have
lots of stuff, I like to modify the menu bar (John Walkenbach's code).

Or you could save the file as a normal workbook (.xls).

You may want to open the workbook, save it as a normal workbook, then use
Windows|Hide to hide the workbook's window.

Then close excel. You'll be asked if you want to save the file (with the
windows hidden). If you answer yes, then when you open the workbook, it won't
be visible. But the macros will be available in that tools|macro dialog.

If you have a personal.xls file, I bet you've done something like this already.

 

[Randy Harmelink]

The macros ARE available through the dialog. They just aren't listed
there. You need to type them in. I have a lot of my add-in macros
either assigned to keyboard shortcuts or something I can click on in
the workbook.

 

[Daniel M]

Thanks! This seems to work ok. I tried to type in the macro name but it didnt
seem to run. do i need a full path? ie: hiddenmacro.xls macro? I did go and
assign a key stroke to it and i was able to get it to run.

Here is my final problem with this...I want to be able to call this from
access. I have access open excel and i have an addin installed on the pc. Now
how do i call the macro in code? I have it as a menu item but i dont know the
code to call a custom menu item, I can assign keystokes to it but how do i
call that in code?

Thanks.

The macros ARE available through the dialog. They just aren't listed
there. You need to type them in. I have a lot of my add-in macros
either assigned to keyboard shortcuts or something I can click on in
the workbook.

but the [add-in] macros themselves do not show up when i do
tools...macros. Is there a way for this to happen?