How to Add Controls to Excel 2016 Dialog Boxes in VBA - dummies

How to Add Controls to Excel 2016 Dialog Boxes in VBA

By John Walkenbach

Here, you learn how to add controls to a VBA UserForm, give them meaningful names, and adjust some of their properties. Before you can do any of these things, you must have a UserForm, which you get by choosing Insert →  UserForm in the VBE. When you add a UserForm, make sure that the correct project is selected in the Project window (if more than one project is available).

Adding controls

Oddly enough, the VBE doesn’t have menu commands that let you add controls to a dialog box. You must use the floating Toolbox to add controls. Normally, the Toolbox pops up automatically when you activate a UserForm in the VBE. If it doesn’t, you can display the Toolbox by choosing View  →  Toolbox.

Follow along to add a control to the UserForm:

  1. Click the Toolbox tool that corresponds to the control you want to add.

  2. Click in the UserForm, and drag to size and position the control.

    Alternatively, you can simply drag a control from the Toolbox to the UserForm to create a control with the default dimensions. Here is a UserForm that contains a few controls: Two OptionButtons (inside a Frame), a ComboBox, a CheckBox, a ScrollBar, and a CommandButton.

    A UserForm in the VBE, with a few controls added.
    A UserForm in the VBE, with a few controls added.

A UserForm may contain vertical and horizontal grid lines, which help align the controls you add. When you add or move a control, it snaps to the grid. If you don’t like this feature, you can turn off the grids by following these steps:

  1. Choose Tools  →  Options in the VBE.

  2. In the Options dialog box, select the General tab.

  3. Set your desired options in the Form Grid Settings section.

Introducing control properties

Every control that you add to a UserForm has properties that determine how the control looks and behaves. You can change a control’s properties at the following two times:

  • At design time — when you’re designing the UserForm. You do so manually, using the Properties window.

  • At runtime — while your macro is running. You do so by writing VBA code. Changes made at runtime are always temporary; they are made to the copy of the dialog box you are showing, not to the actual UserForm object you designed.

When you add a control to a UserForm, you almost always need to make some design-time adjustments to its properties. You make these changes in the Properties window. (To display the Properties window, press F4.) Here is the Properties window, which displays properties for the object selected in the UserForm — which happens to be a CheckBox control.

Use the Properties window to make design-time changes to a control’s properties.
Use the Properties window to make design-time changes to a control’s properties.

To change a control’s properties at runtime, you must write VBA code. For example, you may want to hide a particular control when the user clicks a check box. In such a case, you write code to change the control’s Visible property.

Each control has its own set of properties. All controls, however, have some common properties, such as Name, Width, and Height. Here are some of the common properties available for many controls.

Property What It Affects
Accelerator The letter underlined in the control’s caption. The user
presses this key in conjunction with the Alt key to select the
control.
AutoSize If True, the control resizes itself automatically based on the
text in its caption.
BackColor The control’s background color.
BackStyle The background style (transparent or opaque).
Caption The text that appears on the control.
Left and Top Values that determine the control’s position.
Name The control’s name. By default, a control’s name is
based on the control type. You can change the name to any valid
name, but each control’s name must be unique within the
dialog box.
Picture A graphics image to display. The image can be from a graphics
file, or you can select the Picture property and paste an image
that you copied to the Clipboard.
Value The control’s value.
Visible If False, the control is hidden.
Width and Height Values that determine the control’s width and
height.

When you select a control, that control’s properties appear in the Properties window. To change a property, just select it in the Properties window and make the change. Some properties give you some help. For example, if you need to change the TextAlign property, the Properties window displays a drop-down list that contains all valid property values.

Change some properties by selecting from a drop-down list of valid property values.
Change some properties by selecting from a drop-down list of valid property values.