Working with Controls

Now that your form is set up with the design-time properties you need, you can get down to the brass tacks of form design. In other words, you can start adding controls to the form, adjusting those controls to get the layout you want, and setting the design-time properties of each control.

Inserting Controls on a Form

The new form object is an empty shell that doesn't become a useful member of society until you populate it with controls. As with the form-building tools in Word and Access, the idea is that you use this shell to "draw" the controls you need. Later, you can either link the controls directly to other objects (such as Excel worksheet cells) or create procedures to handle the selections.

The Toolbox contains buttons for all the controls you can add to a form. Here are the basic steps to follow to add any control to the form:

Click the button you want to use.
Move the mouse pointer into the form and position it where you want the top-left corner of the control to appear.
Click and drag the mouse pointer. VBA displays a gray border indicating the outline of the control.
When the control is the size and shape you want, release the mouse button. VBA creates the control and gives it a default name (such as CheckBoxn, where n signifies that this is the nth check box you've created on this form).

If you want to add multiple instances of the same type of control, double-click the appropriate Toolbox button.The button will remain pressed, and you can draw as many instances of the control as you need.When you're finished, click an empty part of the Toolbox to reset the control.

Selecting Controls

Before you can work with a control, you must select it. For a single control, you select it by clicking it. If you prefer to work with multiple controls, the Visual Basic Editor gives you a number of techniques:

Hold down the Ctrl key and click each control.
You also can "lasso" multiple controls by clicking and dragging the mouse. Move the mouse pointer to an empty part of the form, hold down the left button, and then click and drag. The VBE displays a box with a dashed outline, and any control that falls within this box (in whole or in part) will be selected.
To select every control, make sure the form is active and then select Edit, Select All. (For faster service, you can also either press Ctrl+A or right-click an empty part of the form and choose Select All from the shortcut menu.)
To exclude a control from the selection, hold down the Ctrl key and click inside the control.

After you've selected multiple controls, you can set properties for all the controls at once. Note, however, that the Properties window shows only those properties that are common to all the controls. Not only that, but if you size, move, copy, or delete one of the selected controls (as described in the next few sections), your action applies to all the controls.

Each control is surrounded by an invisible rectangular frame. When you select a control, the VBE displays a gray outline that represents the control's frame and this outline is studded with white selection handles at the frame's corners and midpoints.

Sizing Controls

You can resize any control to change its shape or dimensions. The following procedure outlines the steps to work through:

Select the object you want to size.
Position the mouse pointer over the selection handle you want to move. The pointer changes to a two-headed arrow. To change the size horizontally or vertically, use the appropriate handle on the middle of a side. To change the size in both directions at once, use the appropriate corner handle.
Click and drag the handle to the position you want.
Release the mouse button. The Visual Basic Editor redraws the object and adjusts the frame size.

To size the form itself, click an empty part of the form and then click and drag the selection handles that appear around the form.

Moving Controls

You can move any control to a different part of the form by following these steps:

Select the control you want to move.
Position the mouse pointer inside the control. (You can also position the pointer over the control's frame, although you need to make sure the pointer isn't over a selection handle. In this case, the pointer changes to a four-headed arrow.)
Click and drag the control to the position you want. As you drag the object, a dashed outline shows you the new position.
Release the mouse button. The VBE redraws the control in the new position.

Copying Controls

If you've formatted a control and then decide that you need a similar control, don't bother building the new control from scratch. Instead, follow the steps outlined next to make as many copies of the existing control as you need:

Select the control you want to copy.
Hold down the Ctrl key, position the mouse pointer inside the control, and press the left mouse button. The pointer changes to an arrow with a plus sign.
Click and drag the pointer to the position you want. As you drag the mouse, a dashed outline shows you the position of the copied control.
Release the mouse button. The VBE copies the control to the new position.

You also can use the Clipboard to copy controls. In this case, you click the control, choose Edit, Copy, and then choose Edit, Paste. The Visual Basic Editor adds a copy of the control to the form that you can then move to the appropriate position.

Tip: You also can right-click the control and click Copy from the control's shortcut menu.To paste the control, right-click an empty part of the form, and then click Paste. Alternatively, use Ctrl+C to copy a selected control and Ctrl+V to paste it.

Deleting Controls

To delete a control, select it and then choose Edit, Delete. The Visual Basic Editor deletes the control.

Tip: To delete a control quickly, select it and press the Delete key. Alternatively, you can right-click the control and click Delete in the shortcut menu.

Grouping Controls

The Visual Basic Editor lets you create control groups. A group is a collection of controls you can format, size, and move-similar to the way you format, size, and move a single control. To group two or more controls, select them and use any of the following techniques:

Choose the Format, Group command.
Right-click inside any one of the selected controls and click Group from the shortcut menu.
Click the UserForm toolbar's Group button.
Tip: The Visual Basic Editor treats a group as a single control with its own frame. To select an entire group, you need to select just one control from the group.

To ungroup controls, select the group and use one of these methods:

Choose Format, Ungroup.
Right-click inside any one of the selected controls and click Ungroup from the shortcut menu.
Click the UserForm toolbar's Ungroup button.

Setting Control Properties

A form control is an object with its own set of properties. A check box, for example, is a CheckBox object, and it has properties that control the name of the check box, whether it is initially checked, what its accelerator key is, and more.

You can manipulate control properties during program execution (in other words, at runtime), either before you display the form or while the form is displayed. (For example, you might want to disable a control in response to a user's action.) However, you can also set some control properties in the Visual Basic Editor (in other words, at design time) by using the Properties window. To display a particular control's properties in the Properties window, you have two choices:

Click the control in the form.
Select the control from the drop-down list near the top of the Properties window.

Common Control Properties

Later in this tutorial I'll run through each of the default controls and explain their unique features. However, a few properties are common to many of the controls. Most of these properties perform the same function. These properties include the following: BackColor, ForeColor, SpecialEffect, Enabled, Font, Picture, PicturePosition, Height, Width, Left, and Top. (Note that the latter two are relative to the left and top edges of the form.)

Here's a list of a few other properties that are common to some or all of the default controls:

Accelerator-This property determines the control's accelerator key. (In other words, the user can select this control by holding down Alt and pressing the specified key.) The letter you enter into this property appears underlined in the control's caption.
Tip: Some controls (such as list boxes and text boxes) don't have a Caption property.However, you can still assign an accelerator key to these controls by using a Label control.
AutoSize-If this property is set to True, the control resizes automatically to fit its text (as given by the Caption property).
BackStyle-Determines whether the control's background is opaque (use fmBackStyleOpaque) or transparent (use fmBackStyleTransparent).
ControlSource-In the Visual Basic Editor for Excel, this property specifies which cell will be used to hold the control's data. You can enter either a cell reference or a range name.
Caution: The value of a cell linked to a control changes whenever the value of the control changes, even when the user clicks Cancel to exit the form. It's usually better (and safer) to assign a control's value to a variable and then, if appropriate, place the value in the cell by using code in your VBA procedure.
Caption-Sets the control's text, which is usually just the name of the feature represented by the control.
ControlTipText-Sets the "control tip" that pops up when the user lets the mouse pointer linger over the control for a second or two.
Locked-Set this property to True to prevent the user from editing the current value of the control.
TabIndex-Determines where the control appears in the tab order (in other words, the order in which VBA navigates through the controls when the user presses the Tab key).
TabStop-Determines whether the user can navigate to the control by pressing Tab. If this property is set to False, the user can't select the control by using the Tab key.
Visible-Determines whether the user can see the control (True) or not (False). For example, you might want to hide a control until the user selects a particular option.

Setting the Tab Order

As you know, you can navigate a form by pressing the Tab key. The order in which the controls are selected is called the tab order. VBA sets the tab order according to the order you create the controls on the form. You'll often find that this order isn't what you want to end up with, so the Visual Basic Editor lets you control the tab order yourself. The following procedure shows you how it's done:

Select View, Tab Order. (You can also right-click an empty part of the form and click Tab Order in the shortcut menu.) The Visual Basic Editor displays the Tab Order dialog box.
In the Tab Order list, click the control with which you want to work.
Click Move Up to move the item up in the tab order, or click Move Down to move the control down.
Repeat steps 2 and 3 for other controls you want to move.
Click OK.

[Previous] [Contents] [Next]