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?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,317
Office Version
365, 2019, 2016, 2010
Platform
Windows, 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
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
 

Forum statistics

Threads
1,081,563
Messages
5,359,618
Members
400,540
Latest member
JimUSMC

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top