- Dedicated trainers
- Maximum 6 per class
- Superb courseware
Phone (01457) 858877 or email
ASSIGNING SHORT-CUT KEYS TO VBA MACROS IN EXCEL
This short blog shows how to assign short-cut keys to macros in Excel 2010, Excel 2007 and Excel 2003. So if you've written a whizzy macro in VBA and want to be able to run it with a single keystroke combination, read on!
This blog is part of our Excel macros online tutorial series. Have a look also at our Excel courses and VBA courses, each having at most 6 people attending.
Assigning Macros to Short-Cut Keys in Excel
Suppose that you've written a whizzy macro (called, oddly, SpiderPig), and you want to find an easy way to run it.
For more ideas on how to run macros - including assigning them to form buttons, pictures or the quick access toolbar - see this blog on running macros in Excel.
You can press the CTRL key combined with any lower-case or upper-case letter to run your macro, although you'd be advised to stick to upper-case letters. Why? Well:
- Pressing SHIFT + CTRL + S to run your own macro is reasonable (this key doesn't do anything else in Excel); but
- Pressing CTRL + S to run your macro is not (this will override the normal key behaviour, which for this key combination is to save your workbook!).
To assign a short-cut key to your macro is different in Excel 2010, Excel 2007 and Excel 2003, as shown under separate headings below.
Assigning Short-Cut Keys to Macros in Excel 2010
To assign a short-cut key to a macro in Excel 2010, first choose to display a list of macros - one way to do this is to click on the Macros button:
Click on the Developer tab on the Ribbon, then on Macros as shown
See this separate blog for how to display the Excel 2010 Developer tab on the Ribbon if you can't see it.
You can now choose your macro:
To assign a short-cut key to a macro:
- Select the macro to which you want to assign a short-cut key (the file containing the macro must be open).
- Click on the Options... button.
Finally, choose a short-cut key to assign to the macro and select OK:
Type into the Shortcut key box the letter you want to assign to this macro (using upper case for SHIFT + CTRL combinations, as here)
You will now be able to press SHIFT + CTRL + S to run this macro whenever you have the file containing it open.
Assigning Short-Cut Keys to Macros in Excel 2007
This is the same procedure as for Excel 2010, except that the way to display the Developer tab on the ribbon in Excel 2007 is different at the start.
Assigning Short-Cut Keys to Macros in Excel 2003 and Earlier
For earlier versions of Excel, the procedure for attaching short-cut keys to macros is similar. First display a list of your macros:
Select the menu shown on the left to display a list of your macros
You can now proceed as for Excel 2010 (behind the scenes in Excel, nothing much has changed!).
Comments on this blog
This blog currently has no comments.