Does anyone know how to have the Macros in my XLS file NOT have to reference
the orginal file position? I have created a toolbar button that runs a
macro. When I copy this XLS to another machine or rename the file, it can no
longer find the Macro since it is trying to find it in the original file.
The only way I can fix it is to "Assign Macro" again to the button. This is
not good as I need to distribute this XLS to other users. Is there a way
around this? The same problem occurs in the menus.

Thanks, -BOBJ

Hi,

1a. Create/Delete toolbar/menus/command bar buttons on the fly:
In the Workbook_Open sub of the ThisWorkbook module, create the toolbar,
menus, and controls using code.
In the Workbook_BeforeClose sub of the ThisWorkbook module, delete the
toolbar, menus, and controls.
Assign the macro using 2.
1b. For buttons on sheets directly, you can create them manually, then
assign the macro using 2.
2. Also, assign the proper OnAction property to controls based on the book
name when the book opens.
(Right after creating a control/button)
Something like:
Say your macro to be run is MyMacro
Dim macroName as String, cbb as commandbarbutton
macroName="MyMacro"
...
cbb.OnAction = "'" & thisworkbook.name & "'!" & macroname

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"BOBJ" wrote:

Does anyone know how to have the Macros in my XLS file NOT have to reference
the orginal file position? I have created a toolbar button that runs a
macro. When I copy this XLS to another machine or rename the file, it can no
longer find the Macro since it is trying to find it in the original file.
The only way I can fix it is to "Assign Macro" again to the button. This is
not good as I need to distribute this XLS to other users. Is there a way
around this? The same problem occurs in the menus.

Thanks, -BOBJ

I find the easiest way is to create the toolbar when the workbook opens.


If you want to try...

If you want to add an option 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://groups.google.co.uk/groups?th...5B41%40msn.com

BOBJ wrote:

Does anyone know how to have the Macros in my XLS file NOT have to reference
the orginal file position? I have created a toolbar button that runs a
macro. When I copy this XLS to another machine or rename the file, it can no
longer find the Macro since it is trying to find it in the original file.
The only way I can fix it is to "Assign Macro" again to the button. This is
not good as I need to distribute this XLS to other users. Is there a way
around this? The same problem occurs in the menus.

Thanks, -BOBJ

--

Dave Peterson

Hi,

1. Create menus, command bars on the fly with code.
a - when the book opens (in the Workbook_Open sub of the ThisWorkbook
module),
create the controls.
b - assign the macro to the contol with specific book name:
Dim macroName as String, cbb as CommandBarButton
...
cbb.OnAction = "'" & ThisWOrkbook.name & "'!" & macroName
c - Destroy the controls when the book closes (in the Workbook_BeforeClose
sub of the ThisWorkbook module).
2. For controls that are on a sheet, no need of creating/deleting through
code, just re-assign the macro throiugh code when the book opens (see 1b)

--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"BOBJ" wrote:

Does anyone know how to have the Macros in my XLS file NOT have to reference
the orginal file position? I have created a toolbar button that runs a
macro. When I copy this XLS to another machine or rename the file, it can no
longer find the Macro since it is trying to find it in the original file.
The only way I can fix it is to "Assign Macro" again to the button. This is
not good as I need to distribute this XLS to other users. Is there a way
around this? The same problem occurs in the menus.

Thanks, -BOBJ