Macro Shortcut Key

[Guest]

If you have 2 Excel files open which both have macro's named the same and
assigned to the same shortcut key...it does not execute the current sheet's
macro. It runs the other sheets macro because it is listed first in the
global macro listing.
Is there a way to make it run the macro from the workbook that you are in?

ex.
I have 2 files...one called Test1.xls and the other Test2.xls....both having
Macro's assigned to ctrl+shift+Z. If I'm in Test2.xls....it will run the
Test1.xls macro.

Macro List

 

[Henry]

MWSoccer18,

Try changing one of the macro names and, of course, the code for the
associated button.

Henry

 

[Guest]

There is no button involved. The macro is always executed through a
ctrl+shift+Z combination. Changing the macro name will only work if the
macro name in the current sheet alphabetically comes before the macro in the
other sheet. But then...when you switch back to the other sheet...you will
be executing the wrong macro in that sheet.

 

[Henry]

MWSoccer18,

Different shortcut combinations?

Or
In a module put this sub.

Private Sub Chooser()

If Sheets("Sheet1") = ActiveSheet Then
Call test1
Else
Call test2
End If
End Sub

Use your shortcut combination to call Sub Chooser()

Henry

 

[Guest]

OK....that is a good way to get around it...but...what if the macro names are
the same? Here's the problem...We have several revisions of a "cost sheet".
All the macro names are the same because the master is a template that just
gets revised over time. Someone could have 3 or 4 of these files open at
once with different revisions of the macro in each file....but the same macro
name and shortcut key. So when a shortcut key is pressed...the first macro
listed in the Macro List will take precedence.