Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

Module 1 - Getting Started in VBA

Lesson 1.3 - Basic User Interfaces

Topic 1.3.2 - Keyboard Shortcuts

An alternative way to run your subroutines from within the Excel window is to create keyboard shortcuts. A keyboard shortcut provides a quick way to run a subroutine from anywhere within the workbook, without needing to navigate to a menu worksheet in order to click a button.

Files Needed

You can click here to download the sample code used on this page.

Completed Code

You can click here to download a file containing the completed example.

You can assign a keyboard shortcut to a subroutine from within the Excel window. To do this, choose Developer | Macros from the ribbon, or press Alt + F8 on the keyboard.

Choose this option to see a list of procedures to which you can assign keyboard shortcuts.

Select the procedure to which you'd like to assign a keyboard shortcut and click Options...

We'll assign a shortcut to the Insert_New_Sheet procedure.

On the dialog box which appears you can then choose the keyboard shortcut you want to use. You can enter a letter, number, or punctuation character as a shortcut. If you pick a keyboard shortcut which already exists, your new one will take precedence! In the example below, we've entered a lowercase letter i into the text box:

The dialog box shows that we'll need to press Ctrl + i to trigger the shortcut later. This means that we can't use Ctrl + i to italicise the text in a cell!

If you hold Shift while entering the shortcut into the dialog box, you'll also need to hold it to trigger the shortcut later. In the example below we've entered an uppercase I into the text box:

The dialog box shows that we'll need to press Ctrl + Shift + I to run the subroutine later.

To finish creating the keyboard shortcut, click OK on the Macro Options dialog box shown above, then simply close the Macro dialog box:

Click the X or Cancel to close the Macro dialog box.

At this point, your keyboard shortcut is live! Try pressing it to make sure that it performs the task you've assigned it to:

Pressing our keyboard shortcut triggers the subroutine that we attached it to.

Keyboard shortcuts will only work when the workbook in which the code is stored is open. When this workbook is open, pressing the keyboard shortcut in any other open workbook will run the subroutine.

Although keyboard shortcuts certainly provide a quick way to run your code, they do have a few drawbacks:

It isn't obvious to a user that a keyboard shortcut exists - you'll need to teach the user.
You have to rely on your users remembering the shortcuts.
Your shortcuts might replace shortcuts that your user is already familiar with.

Bear these things in mind before you choose to create keyboard shortcuts for your users.

To practise creating and using keyboard shortcuts, try assigning shortcuts to each of the remaining two procedures in the workbook.