Macro Keys

[hercule_p2001]

I use many macros to ease my work and assign short-cut keys to them. I'll explain my problem with an example:

Say I have a 2 workbooks - A and B.
Wkbk A has a macro to do a certain task and I assign it a key Ctrl+g.
Wkbk B also has a macro to do some other task with the same shortcut key of Ctrl+g.
If the workbooks are opened individually one at a time, there is no problem with the macros. BUT if both workbooks are open then even if the active workbook is A, on pressing Ctrl+g, I get an error of subscript out of range and debugging points to a line in the macro of workbook B. Why? What can be done so that the macro in active workbook is the default macro to be played.

Thanks.

[Ken Puls]

I believe that the most recent workbook to be opened gets the shortcut key assigned (it overwrites any previously).

You could deal with this by using code to assign your macro to the keystroke in the Workbook_Activate event. That way each time the workbook is activated it would re-asset control.

I know you post at other forums, and it's been a week since you posted this. Have you got a solution yet and, if so, would you mind linking to it or posting it here? If not, we'd be glad to help.

[hercule_p2001]

No. I haven't posted this question anywhere. Normally I wait for about 7-10 days before I post my question anywhere else. I understand that it is not a good habit to pose the same question at multiple sites. The last one also, I posted here after I did not get a reply from there for quite some time.

Coming to this question, how do I assign the keystroke in the Workbook_Activate event?

[eferrero]

Private Sub Workbook_Activate()
' assign shortcut  Alt-t to macro Macro1
Application.MacroOptions _
    Macro:="Macro1", _
    Description:="My macro does this", _
    ShortcutKey:="t"
End Sub

[hercule_p2001]

eferrero

The vb code seems to work, but I couldn't find the workbook_activate option so put the code in worksheet_activate. I get an error 1004 when I activate a sheet other than the the sheet for which the activate code is written.
Where can I find the Workbook_Activate?

[Ken Puls]

Hi hercule,

The Workbook_Activate event should be placed in the ThisWorkbook code module. It sounds like you put it in the Sheet1 (or equivalent) module.

[hercule_p2001]

Thanks a lot Ken. Works fine now. Appreciate your help.

[ExpertNovice]

I believe that the most recent workbook to be opened gets the shortcut key assigned (it overwrites any previously).

Ken, your answer is correct and I agree your belief should be correct but that is what makes this bug very strange.
Wkbk1: Created years ago using Ctrl+Shift+z to activate a macro. (Z is only one of two keys not already used by Microsoft)
Wkbk2: Created later along with the addition of Ctrl+Shift+z to activate a macro.

Open Wkbk2 and the shortcut works.
Open Wkbk1 and only the shortcut in Wkbk1 works. We both thought this would be the result.
Close Wkbk1 and Wkbk2 works, again.

However.....
Open Wkbk1 and the shortcut works.
Open Wkbk2 and only the shortcut in Wkbk1 works. Neither of us thought this would be the result.
Close Wkbk1 and Wkbk2 works. (Very strange)

PS. I know this post is old but this information may help another, despite it being a very minor bug that would rarely be encountered.