A Macro That Pushes One Button Then When Pushed Again It Pushes a Different

Bottom Line: Larn two unlike ways to create keyboard shortcuts to run macros, and notice the pros and cons for both methods.

Skill Level: Intermediate

Download the Excel File

Y'all tin download the example Excel file I employ in the video here:

Create Your Own Keyboard Shortcuts to Run Macros

Assigning keyboard shortcuts to elementary or circuitous macros can assist you piece of work faster in Excel.  This is especially true if you lot have to perform the same deportment repeatedly.  In this post we wait at two pop means to create the shortcut keys.  These include the Macro Options window and VBA code for the Application.OnKey method.  I as well explicate the pros & cons of each method.

1. The Macro Options Window: Shortcut Primal

Nosotros can use the Macro Options window in Excel to create a shortcut key to phone call the macro.  Here are the instructions on how to set information technology up.

1. First by going to the Programmer tab and clicking on the Macros button.  (If you lot don't see the Developer tab on your ribbon, you lot can add together it using these instructions.) Alternatively, you can use the keyboard shortcut Alt+F8.
   
2. Afterward selecting the macro that y'all want to assign the shortcut to, click the Options button.
   
3. In the Macro Options Window, you lot can create the shortcut you desire past calculation a alphabetic character, number, or symbol.
   

Exist careful not to override an existing shortcut that you oftentimes use, such as Ctrl+C to copy. One way to avert doing this is by adding Shift to the shortcut to brand information technology a bit more complex. In my case, I used Ctrl+Shift+C.

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

two. The Application.OnKey Method in VBA

We tin can too apply VBA lawmaking 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 lot tin can do this past clicking the Visual Basic push on the Developer tab, or pressingAlt+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.

1. Create a new macro and name it CreateShortcut (or any you choose to proper noun the procedure),
2. Add together a new line and start information technology with the control Application.OnKey followed by a space.
   The Awarding.OnKey method has two parameters for the Central and Procedure.  The Key is the keyboard shortcut combination represented by central codes.  The Procedure is the name of the macro that volition be chosen when the cardinal combination is pressed.
   Both parameters are enclosed in quotation marks.
3. In my case I use "+^{C}" for the Cardinal parameter. The + is the code for Ctrl, the ^ is code for Shift, and the C primal is enclosed in curly brackets (or braces).  How are yous supposed to know the code for each central? Microsoft has this helpful certificate, which contains a complete list.
4. Following this code, you are going to name the procedure that you lot desire 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 y'all tin also see in the image above, the lawmaking to delete this process is simple.  I've typed it just beneath the department for creating the shortcut.  Instead of "CreateShortcut" we will phone call it "DeleteShortcut" and we remove the process 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 whatsoever native Excel keyboard shortcuts.  For case, 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 unlike macros all at the same time.

Automate OnKey with Events

However, y'all can actually automate this by using the Workbook_Open and Workbook_BeforeClose events.  Hither are instructions on how to set information technology up (meet the video above for more details).

1. In the VB Editor, double-click on ThisWorkbook in the Projection Window.
   
2. Cull Workbook in the drop-downward box.
   
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 together an event to delete the macro anytime you lot shut the workbook.  Simply choose BeforeClose in the drop-down on the right and call the aforementioned macro.
   

If your macros are stored in your Personal Macro Workbook, yous tin can follow the aforementioned procedure as above.  Checkout this commodity on how to create your own Personal Macro Workbook if yous don't have yours setup yet.

Pros & Cons for Each Method

For both methods, the keyboard shortcut tin can be used on whatever file we have open up in Excel, as long equally 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 upwards and can be a little more appealing to people who might be intimidated by writing code.

Cons: Macro Options Window

1. You are express in terms of the keys that can exist used.  Unfortunately, you can't use special keys such as Home, Cease, Page Up, etc.
2. The user might already have the aforementioned shortcut cardinal assigned, so if you equally the developer assign one for the workbook, you tin can't control which one (yours or the user'south) volition run.  The order information technology runs in is based on the alphabetical club of the macro names in all open workbooks on the user's reckoner.
3. In that location's no list or index of the shortcut keys you lot've created, and at that place is no way to search to observe them.  And 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 aid create this list, simply this takes actress work every time y'all desire to see your shortcuts.

Pros: Application.OnKey Method

1. You tin can easily wait upwards your keyboard shortcuts by by searching the VBA code for the word "onkey."  Use the Detect window (Ctrl+F) in the VB Editor.
2. If multiple macros or workbooks utilize the aforementioned shortcut, you tin control the social club 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 tin create macro buttons in the Ribbon to enable or disable keyboard shortcuts.  Or, we tin can even employ a keyboard shortcut to toggle our keyboard shortcuts. 🙂
4. Y'all are able to utilize special keys besides Ctrl+Shift (such as Alt, Home, Cease, Folio Down, etc.).  The Ctrl+Alt combination gives united states of america a lot more than 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

And then which method is improve for macro shortcuts?  Well, my preferred way is the OnKey method.

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

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

If you similar using keyboard shortcuts to make your work faster and more than 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 permit me know or to enquire any questions yous might take about assigning keyboard shortcuts.

meeksbeerbeen.blogspot.com

Source: https://www.excelcampus.com/vba/keyboard-shortcut-run-macro/

Share this post