Macro names in a Module

[dingasm]

I've found that assigning a macro to a toolbar control employs the
filename as a prefix to the macro name. EX: myfile.xls!macro1. If the
workbook is renamed or a copy made with a different name, the controls
will continue to look for the macro in the original file. Is there any
way to limit the assigned name to the macro name only?

 

[Tom Ogilvy]

No, I believe not. part of the name is the location.

That said, I have never tried

something.OnAction = "!Macro1"

but I think that would prepend a name as well.

 

[Michel Pierron]

Simply:
ThisWorkbook.Name & "!macro1"

MP

 

[STEVE BELL]

Another approach is to have the workbook create the control either on an
existing toolbar or a new toolbar when the workbook opens (and delete it
when the workbook closes)

Remember to add a delete to remove it all before you remake it.
Or check to see if it already exists before you remake it.

 

[excelgurudummy]

I once new a Steve Bell in Florida - any chance you're him

 

[STEVE BELL]

I have family down there, but never lived there.

I originated in Washington, DC and am now in Oregon...

 

[dingasm]

I've tried this suggestion and it doesn't work. Other than substituting
single quotes for doubles in your suggestion, I've entered it exactly
as shown. When I access the button afterwards, the full directory path
has been added to " 'ThisWorkbook.Name&'!macro1". I've also tried
variations such as " 'ThisWorkbook.Name'!macro1 " as well as
subsituting the worksheet for "Name". Activating the control always
indicates "can't find macro". Any ideas what I'm doing wrong?

 

[dingasm]

Okay, I finally got it! Part of the problem was that I was trying to
make the assignment Michael suggest from outside of Visual Basic. Not
gonna work!! Here's what I finally did.
I had already created an auto_open macro for something else. But
defining the button macros there works great. For each button on the
custom toolbar called "SBIC" I added a line like the following to
assign a Module-level macro.

CommandBars("SBIC").Controls("Vendor Order").OnAction = _
ThisWorkbook.Name & "!VendorOrder"

Now, changing the filename has no negative impact upon locating macros.

thanks for all the help. It's much appreciated.