[VBA] How to Assign Macros to Shortcut Keys (Application.OnKey)

Introduction

It can be tedious to run frequently used VBA macros from the “Macros” dialog every time. If you could call a macro with a single shortcut like Ctrl + Shift + C, your efficiency would skyrocket.

By using the VBA Application.OnKey method, you can easily and flexibly assign your favorite macros to any shortcut key you like.

This article explains everything from the basic usage of Application.OnKey to automatically enabling shortcuts when a workbook opens, and even how to call macros stored in your Personal Macro Workbook.

Basic Usage of Application.OnKey

Application.OnKey is a command that configures a specific macro (procedure) to run when a specific key combination (shortcut) is pressed.

Syntax: Application.OnKey "Key", "Macro Name"

Let’s look at a simple example. We will assign a macro named ShowCurrentTime, which displays the current date and time in a message box, to Ctrl + Shift + T.

Sample Code

' The macro you want to assign
Sub ShowCurrentTime()
    MsgBox "Current time is " & Now(), vbInformation
End Sub

' The macro to register the shortcut
Sub RegisterShortcut()
    ' Assign ShowCurrentTime to Ctrl + Shift + T
    Application.OnKey "+^t", "ShowCurrentTime"
    MsgBox "Registered macro to Ctrl + Shift + T.", vbInformation
End Sub

How to Use

1. Paste both the ShowCurrentTime and RegisterShortcut codes into a Standard Module.
2. Run RegisterShortcut once.
3. Now, whenever you press Ctrl + Shift + T, the ShowCurrentTime macro will run.

How to Specify Keys

Specific symbols are used to represent modifier keys.

Key	Symbol in VBA
Shift	+ (Plus)
Ctrl	^ (Caret)
Alt	% (Percent)

Examples:

• ^t → Ctrl + T
• %t → Alt + T
• +^t → Ctrl + Shift + T

Automatically Registering on Workbook Open

Running RegisterShortcut manually every time is a hassle. By using the Workbook_Open event, you can automatically enable the shortcut key when the Excel workbook is opened.

Setup Method

1. Double-click ThisWorkbook in the VBE Project Explorer.
2. Select Workbook from the left dropdown at the top.
3. Select Open from the right dropdown.
4. Write the Application.OnKey line inside the generated Private Sub Workbook_Open().

Code for ThisWorkbook

Private Sub Workbook_Open()
    ' Automatically register the shortcut when this workbook is opened
    Application.OnKey "+^t", "ShowCurrentTime"
End Sub

With this, the ShowCurrentTime macro is available via Ctrl + Shift + T as long as this workbook is open.

Removing (Resetting) a Shortcut Key

If you want to remove a registered shortcut key and return it to Excel’s default function, execute the OnKey method with the second argument (Macro Name) set to an empty string "".

Sub UnregisterShortcut()
    ' Remove the assignment for Ctrl + Shift + T
    Application.OnKey "+^t", ""
    MsgBox "Unregistered Ctrl + Shift + T.", vbInformation
End Sub

If you want to automatically remove the shortcut when the workbook closes, it is good practice to write this code in the Workbook_BeforeClose event.

Calling Macros from the Personal Macro Workbook

Many users store macros in their Personal Macro Workbook (PERSONAL.XLSB) so they can be used in any workbook.

To assign a shortcut to such a macro, you must prefix the macro name with PERSONAL.XLSB!.

Example: If ShowCurrentTime is stored in the Personal Macro Workbook

VBA

Sub RegisterPersonalMacroShortcut()
    Application.OnKey "+^t", "PERSONAL.XLSB!ShowCurrentTime"
End Sub

By explicitly stating the workbook name, Excel knows exactly which macro to call.

Summary

In this article, we covered how to register VBA macros to shortcut keys using Application.OnKey.

• Register easily with Application.OnKey "Key", "MacroName".
• Use the Workbook_Open event to auto-enable shortcuts when the file opens.
• To unregister, set the macro name to "".
• For Personal Macro Workbooks, specify it like "PERSONAL.XLSB!MacroName".

By turning routine tasks into macros and assigning them shortcut keys with the OnKey method, you can make your daily work surprisingly comfortable. Please master this technique to build your own efficient Excel environment.