7

2 Ways to Assign Keyboard Shortcuts to Macros

Bottom Line: Learn two different ways to create keyboard shortcuts to run macros, and discover the pros and cons for both methods.

Skill Level: Intermediate

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

Download the Excel File

You can download the example Excel file I use in the video here:

Assign Keyboard Shortcut To Macro.xlsm (17.8 KB)

Create Your Own Keyboard Shortcuts to Run Macros

Assigning keyboard shortcuts to simple or complex macros can help you work faster in Excel.  This is especially true if you have to perform the same actions repeatedly.  In this post we look at two popular ways to create the shortcut keys.  These include the Macro Options window and VBA code for the Application.OnKey method.  I also explain the pros & cons of each method.

1. The Macro Options Window: Shortcut Key

We can use the Macro Options window in Excel to create a shortcut key to call the macro.  Here are the instructions on how to set it up.

  1. Start by going to the Developer tab and clicking on the Macros button.  (If you don't see the Developer tab on your ribbon, you can add it using these instructions.) Alternatively, you can use the keyboard shortcut Alt+F8.
    https://www.dropbox.com/sh/88073cmb7ynbhms/AABOSlOqoNMJ5hhRMGIdprf4a?dl=0
  1. After selecting the macro that you want to assign the shortcut to, click the Options button.
    Assign Keyboard Shortcut to Macro - Open Options Window
  1. In the Macro Options Window, you can create the shortcut you want by adding a letter, number, or symbol.
    Macro Options Window Choose Shortcut Key

Be careful not to override an existing shortcut that you frequently use, such as Ctrl+C to copy. One way to avoid doing this is by adding Shift to the shortcut to make it a bit more complex. In my example, I used Ctrl+Shift+C.

Assign Keyboard Shortcut to Macro - Ctrl Shift Combination

To delete the shortcut, simply repeat the process for accessing the Macro Options Window and then delete the character that you entered to create the shortcut.

2. The Application.OnKey Method in VBA

We can also use VBA code to create shortcut keys for macros.  The Application.OnKey method allows us to create and delete the shortcuts.  It also gives us more options and flexibility with our keyboard shortcuts.

Start by accessing the VB Editor.  You can do this by clicking the Visual Basic button on the Developer tab, or pressing Alt+F11.

Developer Tab in Excel 2016 with Visual Basic Button to Open VB Editor

Create Shortcuts with OnKey

In the VB editor, we are going to write some simple code to assign a macro to a keyboard shortcut.

  1. Create a new macro and name it CreateShortcut (or whatever you choose to name the procedure),
  2. Add a new line and start it with the command Application.OnKey followed by a space.
    The Application.OnKey method has two parameters for the Key and Procedure.  The Key is the keyboard shortcut combination represented by key codes.  The Procedure is the name of the macro that will be called when the key combination is pressed.
    Both parameters are enclosed in quotation marks.
  3. In my example I use “+^{C}” for the Key parameter. The + is the code for Ctrl, the ^ is code for Shift, and the C key is enclosed in curly brackets (or braces).  How are you supposed to know the code for each key? Microsoft has this helpful document, which contains a complete list.
  4. Following this code, you are going to name the procedure that you want to assign to that combination of keys. In this case, we want the key combination to run the macro called “CellColorGreen”.

VB Code to assign a procedure to keyboard shortcut

Delete Shortcuts with OnKey

As you can also see in the image above, the code to delete this process is simple.  I've typed it just below the section for creating the shortcut.  Instead of “CreateShortcut” we will call it “DeleteShortcut” and we remove the procedure name (“CellColorGreen”) from the code.  The absence of a procedure tells Excel not to assign an action to that combination of keyboard strokes.

This also resets the key combination to any native Excel keyboard shortcuts.  For example, if we were using Ctrl+C instead, the keyboard shortcut would revert back to performing the Copy action when Ctrl+C is pressed.

Both the Create and Delete macros can have multiple lines of code with the OnKey method.  This allows you to setup different shortcuts for different macros all at the same time.

Automate OnKey with Events

However, you can actually automate this by using the Workbook_Open and Workbook_BeforeClose events.  Here are instructions on how to set it up (see the video above for more details).

  1. In the VB Editor, double-click on ThisWorkbook in the Project Window.
    Select ThisWorkbook in the VB Project Window
  2. Choose Workbook in the drop-down box.
    Choose Workbook in the Drop-down
  3. That will add the Workbook_Open event. Add a line of code to call the macro that you've created. In our case, the code would read “Call Module1.CreateShortcut” (without the quotation marks).  You can also add an event to delete the macro anytime you close the workbook.  Just choose BeforeClose in the drop-down on the right and call the same macro.
    Create event when workbook opens, delete event when workbook closes

If your macros are stored in your personal macro workbook, you can follow the same procedure as above.  Check out this post to learn how to create your own personal macro workbook and the advantages of doing so.

Pros & Cons for Each Method

For both methods, the keyboard shortcut can be used on any file we have open in Excel, as long as the file that contains the macros remains open.  Let's look at some of the advantages and disadvantages for each method.

Pros: Macro Options Window

  1. With this method, keyboard shortcuts are really easy to set up and can be a little more appealing to people who might be intimidated by writing code.

Cons: Macro Options Window

  1. You are limited in terms of the keys that can be used.  Unfortunately, you can't use special keys such as Home, End, Page Up, etc.
  2. The user might already have the same shortcut key assigned, so if you as the developer assign one for the workbook, you can't control which one (yours or the user's) will run.  The order it runs in is based on the alphabetical order of the macro names in all open workbooks on the user's computer.
  3. There's no list or index of the shortcut keys you've created, and there is no way to search to find them.  So if you've created several and can't exactly remember, you may have trouble keeping track of what keys are out there.  There are macros that can help create this list, but this takes extra work every time you want to see your shortcuts.

Pros: Application.OnKey Method

  1. You can easily look up your keyboard shortcuts by by searching the VBA code for the word “onkey.”  Use the Find window (Ctrl+F) in the VB Editor.
  2. If multiple macros or workbooks use the same shortcut, you can control the order or priority of the macros that run.  Shortcuts created with the OnKey method will supersede those created with the Macro Options window.  So, running the OnKey method will ensure that the macro it references is run when the shortcut key is pressed.
  3. The keyboard shortcuts are easy to delete or remove.  We can create macro buttons in the Ribbon to enable or disable keyboard shortcuts.  Or, we can even use a keyboard shortcut to toggle our keyboard shortcuts. 🙂
  4. You are able to use special keys besides Ctrl+Shift (such as Alt, Home, End, Page Down, etc.).  The Ctrl+Alt combination gives us a lot more options for shortcut keys.
  5. Dynamic shortcuts can change the procedure that is called based on conditions in the workbook.

Cons: Application.OnKey Method

  1. You must update your code if the macro name changes.
  2. You have to actually run the macro to assign shortcuts.

The Verdict

So which method is better for macro shortcuts?  Well, my preferred way is the OnKey method.

With the OnKey method: it is much easier to find and see all of our shortcuts, we have more key options, and more control over quickly enabling/disabling multiple shortcuts.

There is no perfect solution, but I recommend using the OnKey method if you are going to set up multiple shortcuts in your Personal Macro Workbook.

Create Multiple Shortcut Keys with Application OnKey

If you like using keyboard shortcuts to make your work faster and more efficient, I recommend you check out my posts 18 VBA Macro Shortcuts and also The Best Keyboards for Excel Shortcuts.

I'd love to hear about the most useful shortcuts you've created, or want to create.  Please comment below to let me know or to ask any questions you might have about assigning keyboard shortcuts.

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 7 comments
Matthias - September 15, 2018

Hi Jon,
Thanks for the valuable explanation. A few weeks ago you pointed me to the Onkey method. And I thought of this only as a method to use when there is no Ctrl+Shift shortcut available anymore for a certain letter with Macro Option.

But the tracking and maintenance aspect is indeed relevant. It was today very easy for me to find all my Onkey shortcuts (5), but the very first Macro Option shortcut comment I found in a module was wrong. I had deleted the shortcut as it was in conflict with a built-in shortcut (and I had brought a button to the QAT, so it was also somehow obsolete).

With built-in shortcuts and all the shortcuts for macros or e.g. paste buddy there is the danger that one pushes a wrong combination – and macros can’t be undone. Any ideas for a naming convention that could bring in some structure or grouping and would helps to differentiate more easily?

Thanks again!

Reply
    Denis - October 18, 2018

    Instead of using one shortcut key per macro (e.g. Ctrl+Shift+1, Ctrl+Shift+2), you can use one shortcut key that displays a menu of macros, selectable with a simple (or few) character(s) (e.g. F12 then 1, F12 then 2).

    You can also use a VBA Enterpad to trigger your macros without using any shortcut keys at all.

    Reply
    Jon Acampora - October 18, 2018

    Hi Matthias,
    For macros that clear the undo history you might want to have a Yes/No message box appear before they run.

    Video #4 on this page on The Personal Macro Workbook explains how to add the Yes/No message box to any macro.

    The Yes button on the message box has focus set to it by default. So all you have to do is press Enter when it appears. This is one way to prevent accidental keyboard shortcut presses that are assigned to macros.

    I hope that helps answer your question.

    Reply
Edil - September 14, 2018

Hi Jon,

I enjoiyed this one. I have quite some macro’s in my PMW.
SinceI followed the VB-Pro Course, I’ve setup a custom tab with buttons to run the macros I most use. Now I’m going to add shortcuts as well using the “Application.OnKey”.

Thanks again Jon.

Reply
    Jon Acampora - September 14, 2018

    Awesome! Happy to hear about all the macros in your PMW, and excited for all the progress you’ve made. Thanks Edil! 🙂

    Reply
Mark - September 14, 2018

Excellent, very well explained, thank you…..

Reply

Leave a Reply: