Last updated on April 14, 2023 By Victor Chan
Excel macros are potent tools that can help you automate repetitive tasks or complex workflows, such as formatting data, creating charts, or updating pivot tables.
But how do you run your macros quickly and conveniently? Do you always have to go to the Developer tab and click the Macros button? Or do you have to remember the name of the Macro and type it in the Run dialog box?
There is a better way: macro shortcut keys. Macro shortcut keys are combinations of keys that trigger a macro when you press them.
For example, you can assign Ctrl+Shift+C to run a macro that copies a range of cells and pastes them as values. Or you can assign Ctrl+R to run a macro that refreshes all your pivot tables. I personally find shortcut keys fun to create and use. I think you will too!
If you are familiar with Excel macros, then using macro shortcut keys can make your work faster and easier, especially if you have to run the same macros frequently or on different workbooks.
In this article, you will learn how to create and use macro shortcut keys in Excel using three methods: the Macro Options window, the Application.OnKey method with VBA, and the Quick Access Toolbar. You will also learn some tips and tricks to make the most of your macro shortcut keys.
By the end of this article, you will be able to automate your Excel tasks with macro shortcut keys and save time and effort. Let's get started!
Method 1: The Macro Options Window
Method 2: The Application.OnKey Method in VBA
Table of Shortcut Key Combinations for Application.OnKey method
Shortcut to Run Macro that Refreshes All Pivot Tables
Keycodes For Application.OnKey Are Not Case Sensitive
Method 3: Assign a Keyboard Shortcut to a Macro in the Quick Access Toolbar
Tips and Tricks for Using Macro Shortcut Keys
The Macro Options window is a simple and convenient way to assign a shortcut key to a macro. You can access it from the Developer tab, the View tab, or the keyboard shortcut Alt+F8. Here are the steps to create a macro shortcut key using this method:
Now you can run your macro by pressing the shortcut key that you assigned. The shortcut key will be displayed in the Macro dialog box and in the Visual Basic Editor screen.
To delete or change a shortcut key, repeat the steps above and either delete or replace the character in the Shortcut key box.
Here is an example of how to create a macro shortcut key using this method:
In this example, we have created a macro named CopyPasteValues that copies a range of cells (A1:E10) and pastes them as values in another range (G1:K10). We have assigned Ctrl+Shift+C as the shortcut key for this macro using the Macro dialog box.
Here’s the code for this basic macro:
Sub CopyPasteValues()
Range("A1", "E10").Copy
Range("G1").PasteSpecial xlPasteValues
End Sub
To run this macro, we just need to press Ctrl+Shift+C on our keyboard.
As you can see, this method is very easy and straightforward. However, it has some limitations:
If you want more options and flexibility with your macro shortcut keys, you can use the following method: the Application.OnKey method in VBA.
The Application.OnKey method is another way to create macro shortcut keys using VBA code. To use this you need to be in the VB Editor.
When I first tried this method I was excited because for the first time I realized I could create powerful custom keyboard shortcuts!
You see, this method gives you more options and flexibility with your keyboard shortcuts, such as using function keys (F1-F12), arrow keys, or other special keys like the ALT key.
Here are the steps to create a macro shortcut key using Application.OnKey:
This is what you’ll see in code:
Sub CreateShortcut()
Application.OnKey "+%v", "CopyPasteValues"
End Sub
First, run the new macro CreateShortcut(). Place the cursor inside the subroutine and press F5. This binds the macro CopyPasteValues to the keyboard shortcut Alt+Shift+V.
Now you can run the macro “CopyPasteValues” by pressing the shortcut key that you assigned. In this case, we used Alt+Shift+V.
To remove a shortcut key, use Application.OnKey with only one parameter:
Sub DisableShortcut()
Application.OnKey "+%v", ""
End Sub
This means that the shortcut key Ctrl+Shift+C will be disabled.
Now you might be wondering what keys you can use for shortcuts and how to assign them. Fear not. Here’s a table of shortcut key combinations for your inspiration!
Shortcut Key Combination | VBA Key Code |
---|---|
Ctrl + A | ^a |
Alt + A | %a |
Shift + A | +a |
Ctrl + Alt + A | ^%a |
Ctrl + Shift + A | ^+a |
Alt + Shift + A | %+a |
Ctrl + Alt + Shift + A | ^%+a |
Ctrl + Page Up | ^{PGUP} |
Ctrl + Page Down | ^{PGDN} |
Alt + Page Up | %{PGUP} |
Alt + Page Down | %{PGDN} |
Shift + Page Up | +{PGUP} |
Shift + Page Down | +{PGDN} |
Ctrl + Arrow Up | ^{UP} |
Ctrl + Arrow Down | ^{DOWN} |
Ctrl + Arrow Left | ^{LEFT} |
Ctrl + Arrow Right | ^{RIGHT} |
Shortcut Key Combination | VBA Key Code |
---|---|
Alt + Arrow Up | %{UP} |
Alt + Arrow Down | %{DOWN} |
Alt + Arrow Left | %{LEFT} |
Alt + Arrow Right | %{RIGHT} |
Shift + Arrow Up | +{UP} |
Shift + Arrow Down | +{DOWN} |
Shift + Arrow Left | +{LEFT} |
Shift + Arrow Right | +{RIGHT} |
Ctrl + F1 | ^F1 |
Ctrl + F2 | ^F2 |
... | ... |
Ctrl + F12 | ^F12 |
Alt + F1 | %F1 |
Alt + F2 | %F2 |
... | ... |
Alt + F12 | %F12 |
Shift + F1 | +F1 |
Shift + F2 | +F2 |
... | ... |
Shift + F12 | +F12 |
Here’s another example of how to create a macro shortcut key using the Application.OnKey method, this time for something that could be more useful than copying and pasting values.
In this example, we create a new macro named RefreshPivotTables that refreshes all the pivot tables in the active workbook.
Then we assign Ctrl+Shift+R as the shortcut key for this macro.
Here is the modified VBA code for binding and unbinding shortcuts to both macros:
Sub CreateShortcut()
'Bind ALT+SHIFT+V
Application.OnKey "+%v", "CopyPasteValues"
'Bind CTRL+SHIFT+R
Application.OnKey "+^{R}", "RefreshPivotTables"
End Sub
Sub DisableShortcut()
'Unbind ALT+SHIFT+V
Application.OnKey "+%v", ""
'Unbind CTRL+SHIFT+R
Application.OnKey "+^{R}", ""
End Sub
Sub CopyPasteValues()
Range("A1", "E10").Copy
Range("G1").PasteSpecial xlPasteValues
End Sub
Sub RefreshPivotTables()
'Refresh all the pivot tables in the active workbook
Dim pt As PivotTable
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
For Each pt In ws.PivotTables
pt.RefreshTable
Next pt
Next ws
End Sub
After running the CreateShortcut macro, we can use the new keyboard shortcuts. To run the CopyPasteValues macro we press ALT+SHIFT+V and to run the RefreshPivotTables macro we press CTRL+SHIFT+R.
As you can see, this method gives us more options and flexibility with our macro shortcut keys. However, it also has some drawbacks:
If you are sharp-eyed, you may have noticed that we used Application.OnKey with a lower case “v” and an upper case “R”.
In the context of Excel VBA's Application.OnKey method, the case of the letter (uppercase or lowercase) does not matter.
So for instance, the key codes "+^{R}", "+^r", and "+^R" are all valid and represent the same keyboard shortcut: Shift + Ctrl + R.
Here is an example of assigning a macro to the Shift + Ctrl + R shortcut using each of these variations:
Sub AssignShortcut1()
Application.OnKey "+^{R}", "MacroName"
End Sub
Sub AssignShortcut2()
Application.OnKey "+^r", "MacroName"
End Sub
Sub AssignShortcut3()
Application.OnKey "+^R", "MacroName"
End Sub
All three of these variations assign the same keyboard shortcut (Shift + Ctrl + R) to the macro named "MacroName".
Just remember to replace "MacroName" with the actual name of the macro you want to assign the shortcut to. And unbind the shortcut keys when you don’t need to use them.
The Quick Access Toolbar is the line of icons found at the very top of the Excel window. It uses a built-in shortcut combination to run commands or macros.
You can customize it by adding buttons that will run macros when you click on them. You need an existing macro to use.
To assign a keyboard shortcut combination to a macro button on the QAT, follow these steps:
You can now see and run the macro on the QAT by clicking on its button.
You can also use a keyboard shortcut combination to run the macro. The keyboard shortcut is in the form of Alt + n, where n is the button number.
For example, if your macro button is the fifth button on the QAT, then its keyboard shortcut is Alt + 5. You can see the number above your macro button when you press and release the Alt key on your keyboard.
You can also rearrange or remove buttons by using the up and down arrows or the remove button in the Excel Options dialog box.
Just remember that if you assign a macro from your Personal Macro workbook then it will be available whenever you open Excel. But if you assign a macro from a specific workbook to your Quick Access Toolbar, that macro will only be available if the corresponding workbook is open.
Now that you know how to create and use macro shortcut keys in Excel, here are some tips and tricks to make the most of them:
Here's an example of using Application.OnKey to create a toggle switch that turns a macro on or off with the same shortcut key. In this example, we will use the shortcut Ctrl + T to toggle the macro execution.
First, let's create a macro named "ToggleMacro" that toggles the execution of another macro called "MyMacro":
Option Explicit
Dim MacroEnabled As Boolean
Sub ToggleMacro()
MacroEnabled = Not MacroEnabled
If MacroEnabled Then
MsgBox "Macro is now enabled."
Else
MsgBox "Macro is now disabled."
End If
End Sub
Sub MyMacro()
If MacroEnabled Then
' Your macro code here
MsgBox "Executing MyMacro"
End If
End Sub
Now, we need to assign the Ctrl + T shortcut to the "ToggleMacro" using Application.OnKey:
Sub AssignShortcut()
Application.OnKey "^t", "ToggleMacro"
End Sub
Call the AssignShortcut macro to assign the shortcut. After that, pressing Ctrl + T will toggle the execution of "MyMacro" on and off. You can test this by calling the "MyMacro" macro, which will execute only if the MacroEnabled variable is set to True.
Remember to replace the code inside "MyMacro" with your own macro code.
References
Here are some useful references for learning more about macro shortcut keys in Excel:
Macro shortcut keys are a great way to run your macros faster and easier in Excel. They can save you time and effort, especially if you have to run the same macros frequently or on different workbooks. I love using keyboard shortcuts to speed up my workflow. I hope you do too!
In this article, you learned how to create and use VBA macro shortcut keys in Excel using the Macro Options window, the Application.OnKey method, and the Quick Access Toolbar. You also learned some tips and tricks to make the most of your macro shortcut keys.
Now it's your turn: try creating and using some macro shortcut keys for your own macros and see how they improve your workflow. And if you need more help with macros or VBA, check out our other articles and tutorials on this topic.
Thank you for reading and happy Exceling!
Here are some frequently asked questions about macro shortcut keys in Excel:
Q: What are the pros and cons of using the Macro Options Window method for keyboard shortcuts?
A: Pros:
Cons:
Q: What are the pros and cons of using the Application.OnKey method for keyboard shortcuts?
A: Pros:
Cons:
Q: Which method is recommended for creating keyboard shortcuts in Excel macros?
A: While both methods have their merits, the OnKey method is generally preferred due to its ease of finding and managing shortcuts, greater key options, and better control over shortcut behavior. This method is especially recommended for setting up multiple shortcuts in your Personal Macro Workbook.
Q: How do I find out what shortcut keys are assigned to macros in Excel?
A: There are two manual ways to find out what shortcut keys are assigned to macros in Excel:
Q: How do I change or delete a shortcut key assigned to a macro in Excel?
A: There are two ways to change or delete a shortcut key assigned to a macro in Excel, depending on how the shortcut key was assigned:
Q: How do I run a macro without a shortcut key in Excel?
A: There are several ways to run a macro without a shortcut key in Excel:
Q: How do I record a macro with a shortcut key in Excel?
A: There are two ways to record a macro with a shortcut key in Excel:
Hi, I'm Victor!
Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?
Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.
That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.
Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!
Recommended Posts
Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.
Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.
Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!
Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.
Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.
RECOMMENDED READING
Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.
Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!
Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.