This allows you to setup different shortcuts for different macros all at the same time.
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.īoth the Create and Delete macros can have multiple lines of code with the OnKey method. This also resets the key combination to any native Excel keyboard shortcuts. The absence of a procedure tells Excel not to assign an action to that combination of keyboard strokes. Instead of “CreateShortcut” we will call it “DeleteShortcut” and we remove the procedure name (“CellColorGreen”) from the code. I've typed it just below the section for creating the shortcut. In this case, we want the key combination to run the macro called “CellColorGreen”.Īs you can also see in the image above, the code to delete this process is simple. Following this code, you are going to name the procedure that you want to assign to that combination of keys.How are you supposed to know the code for each key? Microsoft has this helpful document, which contains a complete list. The + is the code for Ctrl, the ^ is code for Shift, and the C key is enclosed in curly brackets (or braces). In my example I use “+^” for the Key parameter.The Procedure is the name of the macro that will be called when the key combination is pressed.īoth parameters are enclosed in quotation marks. The Key is the keyboard shortcut combination represented by key codes. The Application.OnKey method has two parameters for the Key and Procedure. Add a new line and start it with the command Application.OnKey followed by a space.Create a new macro and name it CreateShortcut (or whatever you choose to name the procedure),.In the VB editor, we are going to write some simple code to assign a macro to a keyboard shortcut. You can do this by clicking the Visual Basic button on the Developer tab, or pressing Alt+ F11.
It also gives us more options and flexibility with our keyboard shortcuts. The Application.OnKey method allows us to create and delete the shortcuts. We can also use VBA code to create shortcut keys for macros. 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. One way to avoid doing this is by adding Shift to the shortcut to make it a bit more complex.
Here are the instructions on how to set it up. We can use the Macro Options window in Excel to create a shortcut key to call the macro. I also explain the pros & cons of each method. These include the Macro Options window and VBA code for the Application.OnKey method. In this post we look at two popular ways to create the shortcut keys. This is especially true if you have to perform the same actions repeatedly.
Assign-Keyboard-Shortcut-to-Macro.zip Download Create Your Own Keyboard Shortcuts to Run MacrosĪssigning keyboard shortcuts to simple or complex macros can help you work faster in Excel.