Personal Macro Workbook

[Bobo]

Using Excel 97, is there a way to set the default location
of the Personal Macro Workbook, Personal.xls?

I want to create the personal macro workbook on a server,
so that a user can move between workstations and still be
able to use the macros that he has created.

 

[Bob Phillips]

Bobo,

Here's a way to do it. Move the Personal.xls to the new directory and create
a shortcut in XLStart to that location, and it will open up as before.

Good question, something I should have done long time ago. Only problem is
if the server is down, but that shouldn't stop Excel working, you just lose
that workbook.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Dave Peterson]

Just a suggestion:

Don't name this workbook personal.xls. Excel can only have one workbook with
that name open at a time.
(In fact, every open excel file has to have a different name.) And if the other
users have their own personal.xls workbooks, then something has to give--and it
might be your macros that lose.

Instead, I'd create an addin (CommonTools.xla).

Save it to a nice network location.

Use windows explorer to mark it readonly (to stop file contentions)
(right click properties stuff)

and have the users use Tools|addins to load it.

The bad news. If you wanted the users to use Tools|macro|macros to run your
procedures, you'll have more work to do. Macros in .xla files won't show up in
that dialog.

But you could use John Walkenbach's MenuMaker program to add an entry to the
worksheet toolbar--it works really slick!

http://j-walk.com/ss/excel/tips/tip53.htm