Macro shortcut question

[zapptm]

I have 2 Excel workbooks open that both have macros. When I use the shortcut key to run a macro while in workbook 2, the macro from workbook 1 (the 1st workbook that I opened) runs. Why is this happening and how can I ensure that Excel runs the macro from the workbook that I'm in? Thanks, in advance, for any help you can provide.

 

[C with no eyes]

Don't know why the shortcut key is doing that. Why don't you just insert a form control button and attach the macro to it? It can't get confused then.

 

[Ken Cowen]

If you have two workbooks open and each has a macro, with a shortcut, say ctrl-m, when you type ctrl-m the macro from the first workbook that you opened is the one that runs. I am not aware of any way around that, and trapping the issue and running the intended macro is also something I have not been able to do.
Good luck.
Ken

 

[Greg Truby]

Ken is mostly right. If you've manually set the shortcut keys, whichever workbook's got set first wins; even if you close all workbooks then open wb2 then wb1, wb1 will trump wb2 even if opened second.

However, having said that, you could always utilize the workbook's Activate() event handler to clear any assignments for the key and then assign your. For example, let's say your hotkey was Ctrl+m. You could do like so:

'// code module for workbook two

Private Sub Workbook_Activate()
    With Application
            .OnKey "^m"
            .OnKey "^m", "MacroForWorkbookOne"
    End With
End Sub

'// code module for workbook two

Private Sub Workbook_Activate()
    With Application
            .OnKey "^m"
            .OnKey "^m", "MacroForWorkbookTwo"
    End With
End Sub

 

[Ken Cowen]

Greg
I had pretty much gotten away from using keyboard shortcuts to trigger my menu macros because of that issue. Also, it turned out that I, as a keyboard using person, used them way more than my clients who tend to be more mouse oriented and would way rather click something than type two simultaneous commands. But I will start adding those lines to many of my spreadsheets.
Thanks
Ken

Ken is mostly right. If you've manually set the shortcut keys, whichever workbook's got set first wins; even if you close all workbooks then open wb2 then wb1, wb1 will trump wb2 even if opened second.

However, having said that, you could always utilize the workbook's Activate() event handler to clear any assignments for the key and then assign your. For example, let's say your hotkey was Ctrl+m. You could do like so:

'// code module for workbook two

Private Sub Workbook_Activate()
    With Application
            .OnKey "^m"
            .OnKey "^m", "MacroForWorkbookOne"
    End With
End Sub

'// code module for workbook two

Private Sub Workbook_Activate()
    With Application
            .OnKey "^m"
            .OnKey "^m", "MacroForWorkbookTwo"
    End With
End Sub

 

[zapptm]

Thanks for your feedback! I will give the additional code a try.

 

[Greg Truby]

...Also, it turned out that I, as a keyboard using person, used them way more than my clients who tend to be more mouse oriented and would way rather click something than type two simultaneous commands...

Amen! More than once, as I've been observing a co-worker or client while I help them troubleshoot an Excel issue, I have found myself thinking "oh yeah, I forgot that instead of [some quick keystroke combo] you could also do that by clicking there, and then there, and then there, and then there..." If I want to Copy + Paste Values without using Ctrl+C, Alt+ESV, I have to stop and actually think about it. Normally my fingers have done the task on their own without my even stopping to think about it. The other day I watched an Excel-challenged co-worker scroll down, down, down with her mouse for a couple-thousand rows to select them all. I've told her on at least three occasions she can just hold down her Shift key and do End+DownArrow. Alas, I think the tip is usually forgotten before I am a dozen steps away from her desk.

 

[Ken Cowen]

As my eyesight and fine motor control diminish, those keyboard shortcuts become even more important.