Macro shortcut key stopped working

imperium1980

New Member
Joined
Feb 10, 2009
Messages
27
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?
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,916
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Feb 10, 2009
Messages
27
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

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
Greetings Imperium,

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

Code:
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

Board Regular
Joined
Sep 16, 2008
Messages
161

ADVERTISEMENT

Greetings Imperium,

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

Code:
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

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,672
Messages
5,654,670
Members
418,147
Latest member
dorkas

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
Top