Problems running macro from custom menu item

[RichardB]

Hi folks - I'm trying to assign a small macro to run from
a new menu item in Excel 97. I've create an xla add-in
with the macro in it and added the new menu item via
Customize Toolbar->Commands->Macros->Custom Menu Item.

When I go to the 'Assign Macro' option for the new menu
item, my macro is not listed. I can type it in and it
works fine when I've manually loaded the xla. I then copy
my xla to a network location and set that Default File
Location to this path. When I open Excel, the new menu
option is there ok, but I get the and error saying 'The
macro 'test' cannot be found'.

It's got me stumped unfortunately. I can't find anything
in Excel Help or the MS website, so any help would be much
appreciated.

Thanks.

 

[Tom Ogilvy]

You need to reassign the macro (from it new location) to the button.

in the immediate window you can query the onaction property of the control
to see where it is point. It will be pointing to the wrong location if you
get this message.

 

[RichardB]

Thanks Tom - not sure I follow you. How do I reassign the
button correctly, as I've already done so via the
customize menu option several times without any luck -
does it need to be done elsewhere?

 

[Tom Ogilvy]

You said you assigned it, then moved the file with the macro (as i
understood you). Excel isn't congnizant of that change and apparently
doesn't search for it (default file location would have no effect in this
situation) or you wouldn't get the message. Assign the macro as you did in
the past, but do it after you move the file.

 

[Guest]

Ah, got you now - sorry. I've tried that just now but
unfortunately still get the same error. I've completely
removed the macro assignment and then reassigned it
without any luck.

 

[Tom Ogilvy]

I tied a button to a macro with (using a macro):

commandbars("Custom 2").Controls(1).OnAction = "'C:\Documents and
Settings\togilvy\Application Data\Microsoft\AddIns\File2.xla'!button_click"

I then queried it in the immediate window:

? commandbars("Custom 2").Controls(1).OnAction
'C:\Documents and Settings\togilvy\Application
Data\Microsoft\AddIns\File2.xla'!button_click

When I pressed the button, the Addin was opened and the macro executed.

Perhaps you can do the same (better yet, select the addin in tools=>Addins
so it is automatically loaded)