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
Download the Excel File
You can download the example Excel file I use in the video here:
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.
- 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.
- After selecting the macro that you want to assign the shortcut to, click the Options button.
- In the Macro Options Window, you can create the shortcut you want by adding a letter, number, or symbol.
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.
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.
Create Shortcuts with OnKey
In the VB editor, we are going to write some simple code to assign a macro to a keyboard shortcut.
- Create a new macro and name it CreateShortcut (or whatever you choose to name the procedure),
- 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. - 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.
- 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”.
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).
- In the VB Editor, double-click on ThisWorkbook in the Project Window.
- Choose Workbook in the drop-down box.
- 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.
If your macros are stored in your Personal Macro Workbook, you can follow the same procedure as above. Checkout this article on how to create your own Personal Macro Workbook if you don't have yours setup yet.
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
- 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
- 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.
- 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.
- 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
- 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.
- 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.
- 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. 🙂
- 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.
- Dynamic shortcuts can change the procedure that is called based on conditions in the workbook.
Cons: Application.OnKey Method
- You must update your code if the macro name changes.
- 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.
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.
Excellent, very well explained, thank you…..
Thank you Mark! 🙂
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.
Awesome! Happy to hear about all the macros in your PMW, and excited for all the progress you’ve made. Thanks Edil! 🙂
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!
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.
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.
It is a pleasure to watch a professional at work. Not only are you well versed in Excel but your presentation is well organized AND presented. There is nothing more distracting than listening to a speaker who frequently pauses with ‘ahhs’.
Thank you for the article, but the + is the code for Shift, the ^ is code for Ctrl.
Fantastic Video.
I am pretty familiar with macros, but this video taught me 5-6 things I did not know. It was well presented, and at a great pace. Thanks!
It is MORE QUICK to write the code for shortcut into macro itself.
Please write this way:
Sub MACRO_NAME ()
Attribute MACRO_NAME.VB_ProcData.VB_Invoke_Func = “X\n14”
After this you can write the code you want.
“X\n14” mean that when Ctrl+Shift+X is pressed, macro is started.
You can change X with any other letter.
If the letter is capitalised, then the Shift key is used (and respective letter).
If the letter is NOT capitalised, then just Ctrl key is used (and respective letter).
Keep the good work!
Hello,
I have a macro from a long time ago and I saw that it was already assigned a bunch of shortcut keys. Do you know how I can run a macro to see all my assigned keys? Basically, I was wondering if you would be able to help me find a macro to uncover the problem you stated in Con #3 on the macro options window…
Jon, looks like the macros called by the OnKey method need to be placed in the “ThisWorkbook” or Module. Is there any way to reference a macro that is stored within a specific sheet? I didn’t see it referenced in your video or in the MSDN help page. Any idea on the correct way to format the code to reference a macro stored on a sheet?
Thanks,
Dear:
I wanted to know if a macros is possible which using onkey “% {DOWN}” (alt + down arrow) I immediately enlarged the column width of that cell to 50 and then when leaving that cell the column width is 5 for example.
it’s possible?
Hi
I created a macro for a specific number format and assigned Ctrl + Shift + N to it. The macro will run if I go into the macros window and press Run but not using the shortcut. Any thoughts?
Thanks, Mardi
Hello and thank you for the wonderful education. I am quite new at macros and VBA codes, so playing with some spreadsheets and data that I have put together.
My questions are: –
1. Is there a way of allocating this type of macro / VBA code to a button? e.g. Either a shape or macro button, where when you press on the button it will take you to the end of the spreadsheet and one at the end to return home.
2, If you keep entering data and the end is now further down in the spreadsheet will the button automatically take you to the new end?
Thank you and sorry for my ignorance
Martine
Pl. send me VBA code or any other method to
1. Jump to any worksheets
2. Rename multiple sheets as per a list.
Thanks & Regards
VENU. P.K
[email protected]
Hi,
Could you please help me with creating a macro for different keys the different comments display on Excel sheet.
For ex : if we press Ctrl+shift+A the particular Cell should display as “Tutorial”. And next Ctrl+shift+B the cell should display “Classes”
Please help me to get this.
Thanks you…
Thanks for a great tutorial. FWIW, your video contains an error. At about 10:46 your voiceover says “control alt” but the code on-screen is “+%” which is “shift alt”.
OnKey instructions were very helpful!
tnx
thank you
Annoyingly, i have done this absolutely perfectly but when i press my shortcut keys i get “no cells found” yet when i run the code manually it works without a problem. DAMN YOU MICROSOFT!
Nice presentation. Thank’s!
One question:
I set a macro using this code:
Application.OnKey “^{h}”, “follow_hyperlink”
The problem is that after unsetting this shortcut, the built-in function (Find & Replace) is not triggered anymore and I was not able to find a way to solve this.
Is it even possible or do I have to change the shortcut key combination?
Hi Jon,
Thanks for this. I was wondering if you assigned a keyboard shortcut to a macro on a PC, would this be carried across to someone using the same file on a Mac? I’ve assigned a macro to Ctrl+e on my PC and was told it will be mapped to Option+Cmd+e on the Mac. I don’t have a Mac I can test this on but would like to know if this is true or if the Mac user will need to reassign the shortcut key.