Macro shortcut key stopped working

[imperium1980]

Morning all, I have 3 log sheets with macros dumping various fields into a central database. All 3 have exactly the same code including a shortcut to generate a new blank log page. I have a shortcut 'Ctrl+q' set to do this and for 2 of the spreadsheets this works yet it has stopped working on the third one. I have copied the code from the working ones to the non-working one but to no avail. The same thing happened a year or so ago and there was an option buried in some menu or other I had to re-select but for the life of me I cannot remember what it was. The macro still runs if I manually activate it. Any ideas?

 

[RoryA]

Have you tried opening the workbook, selecting Tools-Macro-Macros from the menu, select the macro in the list, then click the Options button and check the shortcut assignment there?

 

[imperium1980]

Checked and it was assigned. Removed the key and then addded it again and it now works. Don't know what was causing the problem. Thanks.

 

[GTO]

Greetings Imperium,

If you wish, you can also use the activate and deactivate workbook events.

Private Sub Workbook_Activate()
    Application.OnKey "^q", "YourMacroName"
End Sub
 
Private Sub Workbook_Deactivate()
    Application.OnKey "^q"
End Sub

This will prevent the macro from being run if another workbook is open and active.

Hope this helps,

Mark

 

[mtheriault]

Greetings Imperium,

If you wish, you can also use the activate and deactivate workbook events.

Private Sub Workbook_Activate()
    Application.OnKey "^q", "YourMacroName"
End Sub
 
Private Sub Workbook_Deactivate()
    Application.OnKey "^q"
End Sub

This will prevent the macro from being run if another workbook is open and active.

Hope this helps,

Mark

Hello GTO

Could you explain why you use the same "^q" to activate and desactivate. Is it like a toogle switch? When on turn off, when off turn on. Should i always use this "Onkey command like that?

It confuse me

Martin T.
Montreal, Canada

 

[GTO]

Hello GTO

Could you explain why you use the same "^q" to activate and desactivate. Is it like a toogle switch? When on turn off, when off turn on. Should i always use this "Onkey command like that?

It confuse me

Martin T.
Montreal, Canada

Hi Martin,

No, it is not a toggle. Notice that in Activate, I included the name (fake of course) of the macro to be called. In Deactivate, I just listed the shortcutkey combo of Ctrl + q. Leaving out the macro name tells Excel to reset to whatever the default action was.

While you are in VBIDE, open VBA Help and search for "OnKey". If you have any questions, don't hesitate to post 'em

Mark

 

[mtheriault]

Thank you GTO

Well understood why you did it

Martin