Microsoft training courses | Wise Owl - home page

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.

Posted by Andy Brown on 01 July 2011 | no comments

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:

Developer ribbon - Macros icon

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:

Choosing a macro and choosing Options

To assign a short-cut key to a macro:

  1. Select the macro to which you want to assign a short-cut key (the file containing the macro must be open).
  2. Click on the Options... button.
 

Finally, choose a short-cut key to assign to the macro and select OK:

Choosing a short-cut key

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:

Choosing Tools Macros in Excel 2003

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.

All content copyright Wise Owl Business Solutions Ltd 2014. All rights reserved.