Relative Macro Assignments in Custom Menus

[fiorentico]

Hi,

I am trying to import some menus and the corresponding macros assigned
to them from one machine to another. As so, I am over-writing my
PERSONAL.xls file (that is where my macros are stored) and also
over-writing my Excel11.xlb menu file.

The menus pop on the new machine but upon trying to run my macros, I
get a failure. Under the menu buttons, Excel looks for the macros in:

C:\Documents and Settings\"username1"\Application
Data\Microsoft\Excel\PERSONAL.xls

However, on the new machine the location of PERSONAL.xls changes since
there is a different user, "username2". Is there a way to assign a
relative location for the macros? I want Excel to look for the macros
in "PERSONAL.xls" regardless of the user. Thanks


Ivo

 

[Gord Dibben]

Try placing the macros in a new workbook then saving it as an Add-in under
File>Save As>File Type. Scroll down to bottom of list to find Add-in(*.xla)

You will have far less problems for your users.

Just get them to store the add-in in their Office\Library folder and it will
appear on the Tools>Add-ins list.

The menus can be built "on the fly" so you don't overwrite anybody's *.xlb file

See Debra Dalgleish's site for tips on creating Toolbars

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

For menus, here is Dave Peterson's advice..................

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


Gord Dibben MS Excel MVP