VBA Shortcut Key in Excel — Assign Ctrl+Shift Shortcuts to Macros with OnKey and MacroOptions

TL;DR — Excel has two different systems for running a macro from the keyboard. The Macro Options shortcut (Alt+F8 > Options, or Application.MacroOptions in code) is saved inside the workbook and works while that file is open; it only accepts Ctrl+letter or Ctrl+Shift+letter. Application.OnKey is set by code, accepts almost any key including F-keys, and lasts for the whole Excel session, in every workbook, until you reset it. Use Ctrl+Shift+letter, because Ctrl+letter silently replaces Excel's own shortcut, such as Ctrl+C. Reset every OnKey in Workbook_BeforeClose. And if the macro opens a workbook, wait for the Shift key to be released first, or Excel stops your code right after Workbooks.Open.

' System 1: saved in the file, works while this workbook is open
Application.MacroOptions Macro:="FormatReport", _
    HasShortcutKey:=True, ShortcutKey:="R"        ' capital R = Ctrl+Shift+R

' System 2: lives in the Excel session until reset
Application.OnKey "{F9}", "RefreshAll"            ' assign
Application.OnKey "{F9}"                          ' restore Excel's own F9

This is the first article in a cluster on macros that Excel runs for you: from a key, from the clock with OnTime, or from keystrokes you fake with SendKeys. One idea connects them, and it explains most of their surprises: none of them calls your macro directly. Each one puts a request in Excel's queue, and Excel runs it only when it is idle and ready. So you have to plan for the queue: what holds a request up, what removes it, and what is still waiting when the workbook closes.

What you'll learn

• The mental model: two shortcut systems with different scope and lifetime
• Assigning a shortcut in the Macro dialog and with MacroOptions
• OnKey: key codes and the three forms of the call
• Why Ctrl+letter is a trap, and why Ctrl+Alt is a trap in Europe
• The Shift key that stops a macro after Workbooks.Open
• Cleaning up so a key does not outlive its workbook
• Finding which shortcuts a workbook has assigned

The mental model: two systems, two lifetimes

When you press a key in Excel, Excel looks for something to run. It checks two places, and they behave very differently:

The left column is a property of the file. The right column is a change to Excel itself, made by your code and left behind unless your code undoes it. Almost every shortcut bug comes from forgetting which of the two you used.

Both systems share one rule from the cluster: Excel only looks at the key when it is ready. While a cell is in edit mode, while a dialog or a modal UserForm is open, or while another macro is running, the shortcut does nothing.

System 1: the Macro Options shortcut

The manual way: press Alt+F8, select the macro, click Options, and type a letter in the Shortcut key box. A lowercase letter gives Ctrl+letter; holding Shift while you type it gives Ctrl+Shift+letter.

The same thing in code, which is useful when you build or repair a workbook from VBA:

Sub AssignReportShortcut()
    Application.MacroOptions Macro:="FormatReport", _
        Description:="Format the active report sheet", _
        HasShortcutKey:=True, _
        ShortcutKey:="R"            ' "r" = Ctrl+R, "R" = Ctrl+Shift+R
End Sub

The shortcut is saved with the workbook, so save the file afterwards. It works from any open workbook as long as this one is open, and it disappears with the file. That makes it the right choice for a macro that belongs to one workbook: the shortcut travels with the file and cleans up after itself.

The limits: letters only (no F-keys, no Enter), no Alt combinations, and the macro must be a public Sub without arguments in a standard module.

System 2: Application.OnKey

OnKey takes a key code and the name of a procedure:

Application.OnKey "^+r", "FormatReport"    ' Ctrl+Shift+R
Application.OnKey "{F9}", "RefreshAll"     ' F9
Application.OnKey "^{DEL}", "ClearInputs"  ' Ctrl+Delete

The key code uses three prefix characters for the modifiers and braces for named keys:

The rule that matters most is that the same method does three different jobs, depending on the second argument:

Application.OnKey "^+r", "FormatReport"    ' two arguments: run this macro
Application.OnKey "^+r", ""                ' empty string: the key does nothing
Application.OnKey "^+r"                    ' no second argument: Excel's default again

People mix up the last two. To undo your own assignment you want the one-argument form. The empty string is for disabling a key on purpose, for example Application.OnKey "^x", "" to stop users from cutting cells in a template, and it stays disabled until something resets it.

Ctrl+letter replaces Excel's own shortcuts

Assign a macro to Ctrl+C, Ctrl+S or Ctrl+Z and Excel does not warn you. While the workbook is open, the key runs your macro instead of copying, saving or undoing. Ctrl+Z is the worst case: a user presses it to undo a mistake, your macro runs, and a macro cannot be undone either.

Almost every Ctrl+letter is already taken by Excel. Ctrl+Shift+letter mostly is not, which is why the rule is simple: always Ctrl+Shift+letter, in both systems.

On German, French, Spanish and other European keyboards there is a second trap. AltGr is Ctrl+Alt to Windows. Characters such as @, € and the brackets are typed with AltGr: AltGr+Q gives @ on a German keyboard, AltGr+E gives € on many layouts. An OnKey "^%q" (Ctrl+Alt+Q) assignment written on a US keyboard can catch those keystrokes, and the user suddenly cannot type an email address. If the file will be used on European keyboards, avoid Ctrl+Alt combinations entirely.

The Shift key that stops a macro after Workbooks.Open

This one shows up as my macro stops in the middle when I use the shortcut, but works from the Run button. The macro has a Ctrl+Shift shortcut and opens a workbook:

Sub ImportMonthly()                         ' assigned to Ctrl+Shift+I
    Workbooks.Open "C:\Data\monthly.xlsx"
    MsgBox "Imported"                       ' never reached from the shortcut
End Sub

Holding Shift while a workbook opens is Excel's signal to skip that file's startup macros. When you start the macro with Ctrl+Shift+I, your finger is still on Shift when Workbooks.Open runs, and Excel ends the running code along with the startup macros. No error, no message: the code simply stops.

The fix is to wait until Shift is released before opening anything:

#If VBA7 Then
    Private Declare PtrSafe Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
#Else
    Private Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
#End If
Private Const VK_SHIFT As Long = &H10

Sub ImportMonthly()
    Do While GetKeyState(VK_SHIFT) < 0      ' negative = Shift is held down
        DoEvents
    Loop
    Workbooks.Open "C:\Data\monthly.xlsx"
    MsgBox "Imported"
End Sub

The DoEvents guide explains why the loop needs DoEvents: it lets Excel read the key release. Alternatively, give macros that open files a shortcut without Shift through OnKey, such as an F-key.

Cleaning up: a key must not outlive its workbook

An OnKey assignment belongs to Excel, not to the workbook. Close the workbook and the key still points at the macro: pressing it can reopen your file to run the macro, or fail with Cannot run the macro. So the pattern is always a pair, set on open and reset on close:

' In ThisWorkbook
Private Sub Workbook_Open()
    Application.OnKey "{F9}", "RefreshAll"
    Application.OnKey "^+r", "FormatReport"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.OnKey "{F9}"                ' one argument = restore default
    Application.OnKey "^+r"
End Sub

If users work in several workbooks at once and the keys should only apply to yours, use Workbook_Activate and Workbook_Deactivate instead. The Workbook_Open and BeforeClose guides cover those events, including the case where the user cancels the close.

Keep the list of keys in one place, for example a SetKeys(enable As Boolean) procedure that both events call, so a key added later cannot be forgotten in the reset.

Which shortcuts does this workbook have?

There is no property that lists Macro Options shortcuts. Excel stores each one as a hidden attribute on the procedure, which you can see if you export the module (right-click > Export File) and open the .bas file in a text editor:

Sub FormatReport()
Attribute FormatReport.VB_ProcData.VB_Invoke_Func = "R\n14"

R is the key, uppercase meaning Ctrl+Shift. The line is invisible in the VBA editor, which is why a shortcut can survive for years after everyone has forgotten it. OnKey assignments are not stored anywhere, so the only list is your own Workbook_Open code, one more reason to keep them in one procedure.

The judgment call: file shortcuts for files, OnKey for tools

Use the Macro Options shortcut for a macro that belongs to one workbook. It is saved with the file, ends with the file, and needs no cleanup code.

Use OnKey only when you need what it alone offers: F-keys and other non-letter keys, or a shortcut that an add-in or Personal.xlsb provides in every workbook. Then treat it as a change to the user's Excel that you must undo, with a reset for every key you set.

In both cases: Ctrl+Shift+letter, never Ctrl+letter, never Ctrl+Alt on files that go to European keyboards. A shortcut is only helpful if it does not break a key the user already relies on.

How ExcelMaster helps

Shortcut problems are hard to see: the assignment is a hidden attribute or a line in an event procedure, and the symptom is a key that copies nothing, or a macro that stops without an error.

ExcelMaster reads the workbook's modules, lists every shortcut and OnKey assignment it finds, flags the ones that override Excel's own keys or are never reset, and can write the matching Workbook_Open and Workbook_BeforeClose pair for you.

Frequently asked questions

How do I assign a shortcut key to a macro in Excel?

Press Alt+F8, select the macro, click Options, and type a letter in the Shortcut key box. Hold Shift while typing the letter to get Ctrl+Shift+letter, which avoids replacing Excel's own shortcuts. In code, use Application.MacroOptions with HasShortcutKey:=True and ShortcutKey:="R".

What is the difference between OnKey and a macro shortcut key?

A Macro Options shortcut is saved in the workbook and works while that file is open, but only with letters. Application.OnKey is set by code, works with almost any key including F-keys, and lasts for the whole Excel session until it is reset.

How do I reset a key set with Application.OnKey?

Call OnKey with only the key code, for example Application.OnKey "{F9}". Passing an empty string as the second argument does not reset the key; it disables it.

Why does my macro stop after Workbooks.Open when I use the shortcut?

Holding Shift while a workbook opens tells Excel to skip its startup macros, and that also ends your running code. Wait in a loop with GetKeyState until Shift is released, or use a shortcut without Shift.

Can I use F-keys as macro shortcuts in Excel?

Not in the Macro Options dialog, which only accepts letters. Use Application.OnKey "{F9}", "MyMacro" in Workbook_Open, and reset it with Application.OnKey "{F9}" in Workbook_BeforeClose.

Tested in

Tested in: Excel 365 (Windows 11), VBA 7.1 — last verified 2026-10-03.

Related guides: VBA OnTime · VBA SendKeys · VBA Workbook_Open · VBA Workbook_BeforeClose · VBA DoEvents · VBA Open Workbook · VBA Application.Run · VBA Sub