Macros reassigned by XL?

[David B. Elson]

Hi,

I'm trying to figure out a way to keep Excel 97 from changing where it looks
for a macro when called from a button or a menu entry.

I.E.

If there are 2 or more workbooks opened with similar buttons or Menu
entries, Excel seems to default to running macros in the last workbook
opened. After that point, those buttons and menus will only run a macro if
that last workbook is opened, or all buttons etc. are manually reassigned to
the macros in the opened book.

It there either a way to prevent this, or to save the assignments somehow so
they can be restored without having to manually redo each one?

Thanks,
David

--
"We Are Grey. We stand between the dark and the light."

[Tom Ogilvy]

You can redo them with code. I suspect it calls the last open workbook
because the existing button was created by the last open workbook. If that
is not the case, then perhaps it is just coincidence that the button was
created when the last open workbook was open. The button's onaction
property stores the fullpath/name for the workbook where the macro is
located and is not changed dynamically (unless you rename the workbook doing
a saveas.

--
Regards,
Tom Ogilvy

"David B. Elson" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I'm trying to figure out a way to keep Excel 97 from changing where it
looks
> for a macro when called from a button or a menu entry.
>
> I.E.
>
> If there are 2 or more workbooks opened with similar buttons or Menu
> entries, Excel seems to default to running macros in the last workbook
> opened. After that point, those buttons and menus will only run a macro
if
> that last workbook is opened, or all buttons etc. are manually reassigned
to
> the macros in the opened book.
>
> It there either a way to prevent this, or to save the assignments somehow
so
> they can be restored without having to manually redo each one?
>
> Thanks,
> David
>
> --
> "We Are Grey. We stand between the dark and the light."
>
>

[Jan Karel Pieterse]

Hi David,

> I'm trying to figure out a way to keep Excel 97 from changing where it looks
> for a macro when called from a button or a menu entry.
>

Have a look at my tutorial about distributing macro's here:

http://www.jkp-ads.com/Articles/DistributeMacro00.htm

Look for chapter 3 and 4 about menus and toolbars.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com

[David B. Elson]

Guys,

Thanks for the info.

David

"David B. Elson" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I'm trying to figure out a way to keep Excel 97 from changing where it
> looks for a macro when called from a button or a menu entry.
>
> I.E.
>
> If there are 2 or more workbooks opened with similar buttons or Menu
> entries, Excel seems to default to running macros in the last workbook
> opened. After that point, those buttons and menus will only run a macro
> if that last workbook is opened, or all buttons etc. are manually
> reassigned to the macros in the opened book.
>
> It there either a way to prevent this, or to save the assignments somehow
> so they can be restored without having to manually redo each one?
>
> Thanks,
> David
>
> --
> "We Are Grey. We stand between the dark and the light."
>