
Drawing UserForms in the VBA code editor
Part seven of a seven-part series of blogs

Learn how to create your own custom dialog boxes in VBA, using UserForms. This is the first part of a three-part series (the other parts are on Writing Code for UserForms and Creating Advanced Controls).

  1. An Introduction to UserForms in VBA
  2. Creating and Using UserForms in VBA
  3. The Properties Window
  4. Selecting Forms and Controls
  5. Formatting Forms and Controls
  6. Grouping, Aligning, Sizing, Spacing and Arranging
  7. Controlling How Form Keys Work (this blog)

This blog is part of our Excel VBA tutorial series.  We also run training classes in Excel and courses in VBA.

Posted by Andy Brown on 28 February 2012


Controlling How Form Keys Work

If you've been reading this far (or you've just dipped into this blog), you'll probably already know how to format a form.  This page shows you how to make sure that short-cut keys, and the Enter, Tab and Cancel keys work as you would wish for a form.

Controlling Tab Order

What will happen on a form when you press the Tab key?  The answer depends on the tab order of the form:

Control with focus

What will happen when you press Tab?  You want the cursor to jump to the next control which can "receive focus" - that is, the Drink: combobox.


To set this tab order, either select the form or a Frame or MultiPage control on it. If you select the form, you'll see the form's tab order:

The Tab Order menu

Either select the form itself, or a Frame or MultiPage control on it, then select to change the tab order as shown.


If you have a Frame or MultiPage selected, you'll only see some of the form's controls:

Tab order for controls in a frame

Because you have a frame selected, you can change the tab order of the controls within the frame only.


It may seem odd that the tab order includes labels, when these can't receive the focus (you can't click on them) and hence are nothing to do with tab order.  Read on to find out how accelerator keys require labels to appear in the Tab Order dialog box.

Accelerator Keys (Short-Cut Keys)

In the form below, you can press ALT + D to jump to the Drink: combo box, since the label has the D underlined:

A control with focus

The underlined letter D is called an accelerator key.


You can only attach accelerator keys to labels, not textboxes or comboboxes.  What will happen when you press ALT + D for the above example is that the cursor will jump to the next control which can receive the focus following the Drink combobox in the tab order.

Tab order for a form

The reason that you will go to the Drink: combobox above is that it is the first control after the lblDrink label which can receive focus (ie which you can click in).


As to how you attach accelerator keys - that's the easy bit:

The accelerator key property

Just type in a letter for the oddly-named Accelerator property .


The Default Button (the ENTER Key)

When a form appears in Windows, usually one button has a slightly thicker border.  When you press Enter, this is the one Windows will select:

Order button as default button

Here the Order button has a slighly darker border, and is the one which will be chosen if you press Enter.


To make a button the default, assign its Default property to True:

Default property

Select the Order button for our example, and set the Default property to True.


Note that you can only have one default button on a form - so if you make another button the default, this will deselect this one.

The Cancel Button (the ESCAPE Key Button)

Similar to the Default button above, the Cancel button is the one that Windows will choose when you press ESC.

Cancel property

Select the Cancel button, and set its Cancel property to True.  When a user presses ESC, this will then run any code attached to this button.  Just like for the Default button, you can only have one Cancel button per form.


That's the end of this marathon on-line training on formatting user forms.  Next up, we'll learn:

  • how to attach code to forms to make them work correctly
  • how to create some of the more advanced form controls

These, however, are the subject of separate blogs.


This blog has 0 threads Add post