I have an Excel XP file that has some macros stored in a module. These
macros are used in a custom toolbar.  I didn't want the end users to
be able to see the macros when they choose Tools, Macro, Macros, so I
used Option Private Module in the VBA module.  I also set a password
on the VBA project, so it's not viewable either.

When I go into the customize area for the toolbars and I look at the
macro's that are assigned to a particular menu item, it displays
'filename.xls'!mnuMacro which works great.  The problem is that after I
move the Excel file, then the reference to the macro's includes the
path of the original file ie. 'c:\my document\..filename.xls!mnuMacro

I need to be able to put this on other users computers, so it needs to
always refer to the current file.

Any ideas, or workarounds.
Thanks

---

Request for Question Clarification by answerguru-ga on 04 Nov 2003 09:29 PST

Hi there,

Have you tried storing the macro in ThisWorkbook? If your users are
always going to be working within a specific file to invoke the macro
there is really no need to place your code in a seperate module. Plus,
you change the scope of the macro by putting it in this area so it
wouldn't contain and directory information.

I've posted this (informally) as a clarification as I'm not sure if it
would work in your case. Please do let me know if it does the trick
and I will post this as an official answer :)

Thanks,
answerguru-ga

---

Clarification of Question by softcom-ga on 04 Nov 2003 10:07 PST

Hi

Actually, there is a second Excel file involved.  The first file, has
the toolbar, so I think it needs to be in a module.  The second file
has access to the first files toolbar, along with all the functions
that it provides.  I'll try it in the workbook, just to be sure.

---

Clarification of Question by softcom-ga on 04 Nov 2003 14:46 PST

I tried moving it into the workbook, but that just caused a host of other problems.

Any other ideas?

---

Request for Question Clarification by answerguru-ga on 04 Nov 2003 15:51 PST

Unfortunately not...perhaps another researcher will be able to assist you.

answerguru-ga

---

Clarification of Question by softcom-ga on 04 Nov 2003 16:12 PST

I've been able to narrow down the problem further.  Try the following:
- Start a new workbook.
- Record a macro in the workbook.
- Create a button on the toolbar and assign the macro to the button
- save the file
- close excel and then copy the file to a new location on your hard
drive
- open the copy and try the button
The reference for the button gets changed to the original location of
the file, so that when you click on it, excel tries to open the
original file, but can't because the files have the same name. If you
go and modify the path for the button - removing the path portion,
then it works fine.

I searched MSDN for some "advice" on your situation, but Microsoft's
seach facilities are notoriously hard to use and did not produce
anything helpful.

I'd suggest that editing the file path to be "." (current directory)
might prove satisfactory, assuming that the files that you need to
move will always reside in the same directory.

I'll give your directions (for reproducing the problem) a try and test
this idea myself.

regards, mathtalk-ga

Have you tried putting all of the macros into a single file and then
saving that file as an XLA (add-in) for excel.  This file can then be
added to all of the users computers and will not care about the
location.  I had some of the same problems until I did it this way. 
Something that you need to be careful about is when saving the file as
an xla the file in no longer editable so you will need to keep the
original in case you want to make some changes or updates.

Deitzgen

There's actually 2 files involved, so I'm don't think that will work.

I came across a solution on my own.  I create the commandbar and the
menus on it programmatically everytime the file is opened, then when
the file is closed, I delete the commandbar programmatically.  It's a
solution that will do the trick, but I sure thought there should have
been an easier way.

Thanks for all your comments.