Run active workbook's macro even if another have the same shourtcut key

MeiR_ct

New Member
Joined
Feb 22, 2009
Messages
43
Hi all.

I work with two workbooks open together.
I have a quite similar macro in both of them (but each one does the work in its own workbook). That's why they have the same shortcut key.

I've read that if there are multiple macros with the same shortcut key assigned, Excel will run only the macro that appears first alphabetically.
[http://www.bettersolutions.com/excel/ECC653/LC216621011.htm , step 3]
It is possible to assign the same shortcut key to more than one macro in the same workbook using the (Tools > Macro > Macros) dialog box.

You cannot assign the same shortcut key though from the Record Macro dialog box.

If there are any macros that have the same shortcut key assigned, the macro that appears first alphabetically will be run.

What is the best to bypass it, so the active workbook's macro will be always the one to run?
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Hello,

Maybe use an IF to determine which the activeworkbook is, and then run the macro. Like:

<font face=Courier New><SPAN style="color:#00007F">If</SPAN> ActiveWorkbook.Name = "Book1" <SPAN style="color:#00007F">Then</SPAN><br><SPAN style="color:#007F00">'''run macro or code for "book1"</SPAN><br><SPAN style="color:#00007F">Else</SPAN><br><SPAN style="color:#007F00">'''run macro or code for other workbook</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


so basically you have both macros in one procedure. ( I suppose you could have the same contents in both workbooks with the same shortcut).


Other than that, I am not sure how to use that same shortcut for two different macros.
 
Upvote 0
There are 2 problems:
1. With your way, only one workbook will contain the macro, and it will have to be open. I don't work always with same two workbooks, they get changed from time to time. Copying and changing the vba code from one to another can be really frustrating.
2. The macro's action isn't exactly similar in every workbook, it is suited to the needs in each one.
 
Upvote 0
Yes, however you could have the same macro in both workbooks. That would solve number 1

Are there more than two workbooks?
 
Upvote 0
I even thought about assigning different shortcut keys, but except the confuse, Excel will listen to the key combination also when the workbook isn't active. That makes a mess.

And let's assume I have only two workbooks open at once.
 
Upvote 0
I thought of a solution! :)
Code:
Private Sub Workbook_Activate()
    Application.OnKey "^a", "MyMacro"
End Sub

Private Sub Workbook_DeActivate()
    Application.OnKey "^a"
End Sub
I've put this code in both of workbooks, inside their "ThisWorkbook" module.

This way, the shortcut will get enabled when activating one workbook.
On deactivation, it will get disabled (omitting the second argument from "OnKey" reverts the default action for the key combination).
Then when moving to the other workbook, its own code will enable the shortcut again, only for that workbook.


Thanks a lot for your time and help, repairman615. :)
 
Upvote 0
A little fix for those who will use this thread in future.
The code I gave above can result error 1004.
This is the correct syntax, with braces in first argument:
Code:
Private Sub Workbook_Activate()
    Application.OnKey "^{a}", "MyMacro"
End Sub

Private Sub Workbook_DeActivate()
    Application.OnKey "^{a}"
End Sub
Good luck!
 
Upvote 0

Forum statistics

Threads
1,224,587
Messages
6,179,733
Members
452,939
Latest member
WCrawford

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top