SHOP NOW

How to Run a Macro in Microsoft Excel by Assigning a Shortcut Key

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

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:

  1. 1. Go to the Developer tab and click on the Macros button. Alternatively, press Alt+F8 to open the Macro dialog box.
  2. 2. Select the Macro that you want to assign a shortcut key to and click on Options.
  3. 3. In the Macro Options window, enter a letter, number, or symbol in the Shortcut key box. The shortcut key will be in the form of Ctrl+<letter> or Ctrl+Shift+<letter>. For example, if you enter C, the shortcut key will be Ctrl+C. If you enter Shift+C, the shortcut key will be Ctrl+Shift+C.
  4. 4. Click OK to save your changes and close the window.

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:

  • You can only use letters, numbers, or symbols as shortcut keys. You cannot use function keys (F1-F12), arrow keys, or other special keys like Home, End, Page Up, or Page Down.
  • You can only use Ctrl or Ctrl+Shift as modifiers. You cannot use Alt or other combinations.
  • You may accidentally override existing shortcut keys that are used by Excel or other applications.

If you want more options and flexibility with your macro shortcut keys, you can use the following method: the Application.OnKey method in VBA.

Method 2: 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:

  1. 1. Go to the Developer tab and click on Visual Basic. Alternatively, press Alt+F11 to open the Visual Basic Editor (VBE).
  2. 2. In the VBE, create a new module or use an existing one where you want to write your code.
  3. 3. Create a new subroutine (Macro) and name it something like CreateShortcut (or whatever you choose).
  4. 4. In your subroutine, type Application.OnKey followed by a space. This is the VBA method that allows you to create or delete keyboard shortcuts for macros.
  5. 5. The Application.OnKey method has two parameters: ‘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 you press the Key.
  6. 6. Enclose both parameters in quotation marks and separate them by a comma. For example: Application.OnKey "+%v", "CopyPasteValues". This means that when you press Ctrl+Shift+V, it will run a macro named CopyPasteValues.
  7. 7. Press Enter.

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!

Table of Shortcut Key Combinations for Application.OnKey method

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

Shortcut to Run Macro that Refreshes All Pivot Tables

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:

  • You need to know the key codes for each key that you want to use as a shortcut key. You can find a list of key codes here. This page provides a table of key codes for different keys and modifiers, such as function keys, arrow keys, Ctrl, Shift, Alt, etc. For example, the key code for F5 is "{F5}", the key code for Ctrl is "^", and the key code for Ctrl+F5 is "^{F5}".
  • You need to write VBA code for each shortcut key that you want to create or delete. This can be tedious and error-prone if you have many macros and shortcut keys.
  • You need to run the code that creates the shortcut keys every time you open the workbook or start Excel. Otherwise, the shortcut keys will not work.

Keycodes For Application.OnKey Are Not Case Sensitive

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.

Method 3: Assign a Keyboard Shortcut to a Macro in the Quick Access Toolbar

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:

  • Click the File tab and then click Options. This will open the Excel Options dialog box.
  • In the Excel Options dialog box, click Quick Access Toolbar on the left side. This will show you the options for customizing the QAT.
  • In the Choose commands from drop-down list, select Macros. This will show you all the macros that are available in the current workbook or in your Personal Macro Workbook.
  • Select the macro that you want to add to the QAT and click Add. The macro will be added to the list of buttons on the right side.
  • To change the name or icon of the macro button, select it in the list and click Modify. This will open a small window where you can type a new name and choose a new icon for the button. Click OK when you are done.
  • Click OK again to close the Excel Options dialog box.

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.

Tips and Tricks for Using Macro Shortcut Keys

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:

  • Be careful not to override existing shortcut keys that you frequently use, such as Ctrl+C to copy or Ctrl+Z to undo. One way to avoid this is to use more complex combinations, such as Ctrl+Shift+<letter> or Ctrl+Alt+<letter>.
  • You can also use function keys (F1-F12), arrow keys, or other special keys as shortcut keys, but make sure they don't conflict with other applications or commands. For example, F1 is usually used for Help, and F5 is used for Refresh.
  • You can assign multiple shortcut keys to the same Macro or assign the same shortcut key to different macros in different workbooks. However, this can cause confusion and errors, so it's better to keep your shortcut keys consistent and unique.
  • You can use the Application.OnKey method to create dynamic shortcut keys that change depending on the context or condition. For example, you can use an If statement to assign different shortcut keys based on the value of a cell or the name of the active workbook.
  • You can also use the Application.OnKey method to create toggle switches that turn a macro on or off with the same shortcut key. For example, you can use a Boolean variable to store the state of the Macro and change it every time you press the shortcut key.

Bonus: Macro Toggle Switch

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:

Summary: Run Your Macros with Keyboard Shortcuts!

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!

FAQs: Excel Macro Shortcut Keys

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:

  • Easy to set up, making it appealing for those intimidated by writing code.

Cons:

  • Limited key options, as you can't use special keys such as Home, End, or Page Up.
  • Potential conflicts with existing user-assigned shortcuts, leading to unpredictable behavior based on alphabetical macro name order.
  • Difficulty in tracking assigned shortcuts, as there is no built-in list or index. You can create a macro to create a list, but that's extra work.

Q: What are the pros and cons of using the Application.OnKey method for keyboard shortcuts?

A: Pros:

  • Easy to find assigned shortcuts using the Find window (Ctrl+F) in the VBA Editor by searching VBA code for the word "onkey."
  • Greater control over shortcut priority and behavior. Shortcuts created with OnKey take priority over those created using the Macro Options window. This means that using the OnKey method will ensure that the Macro it refers to is executed when the shortcut key is pressed.
  • Easy to delete or remove shortcuts, with options to create macro buttons or toggles.
  • More key options, including special keys (Page Up, Page Down) and the Ctrl+Alt combination.
  • Dynamic shortcuts can change the called procedure based on workbook conditions.

Cons:

  • Requires updating code if the macro name changes.
  • Must run the Macro to assign shortcuts.

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:

  • In the main Excel window, click on the Developer tab and click on Macros. This brings up the Macro dialog box, in which you will see a list of macros. Click on a macro and click on Options. The Macro Options dialog box shows the assigned shortcut key (if any).
  • In the Visual Basic Editor (VBE), open the module where your macros are stored. In the code window, you will see comments at the top of each Macro that indicate the shortcut keys that were assigned when the macro was recorded (if any). This isn't updated when the shortcut key is changed, so don't rely on these comments.

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:

  • Macro dialog box: Go to the Developer tab and click on Macros. In the Macro dialog box, select the Macro that you want to change or delete the shortcut key for and click on Options. In the Macro Options window, either change or delete the character in the Shortcut key box and click OK.
  • Application.OnKey: Go to the Visual Basic Editor (VBE) and open the module where your macros are stored. In the code window, either change or delete the line of code that uses the Application.OnKey method to create or delete the shortcut key for the Macro.

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:

  • Go to the Developer tab and click on Macros. In the Macro dialog box, select the Macro that you want to run and click on Run.
  • Go to the View tab and click on Macros. In the Macro dialog box, select the Macro that you want to run and click on Run.
  • Press Alt+F8 to open the Macro dialog box. Select the Macro that you want to run and click on Run.
  • Go to the Visual Basic Editor (VBE) and open the module where your macros are stored. In the code window, place your cursor inside the Macro that you want to run and press F5.

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:

  • Go to the Developer tab and click on Record Macro. In the Record Macro dialog box, enter a name for your Macro and optionally a description. In the Shortcut key box, enter a letter, number, or symbol for your shortcut key. The shortcut key will be in the form of Ctrl+<letter> or Ctrl+Shift+<letter>. Click OK and perform your actions. When you are done, click on Stop Recording.
  • Press Alt+T+M+R to start recording a macro. In the Record Macro dialog box, enter a name for your Macro and optionally a description. In the Shortcut key box, enter a letter, number, or symbol for your shortcut key. The shortcut key will be in the form of Ctrl+<letter> or Ctrl+Shift+<letter>. Click OK and perform your actions. When you are done, press Alt+T+M+R again to stop recording.

Connect on YouTube, LinkedIn, Twitter.

Hey, I'm Victor Chan

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.

Learn VBA

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 help now!

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!

Get it here

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.

Learn More

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.

Find out...

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!

Read on

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.

Show me!

JOIN FREE EMAIL NEWSLETTER

Step up your Excel game! Join our free email newsletter and get updates on how to become more awesome at Excel.