Types of Form Controls

The default Toolbox offers 14 different controls for your custom forms. The next few sections introduce you to each type of control and show you the various options and properties associated with each object.

Command Buttons

Most forms include command buttons to let the user accept the form data (an OK button), cancel the form (a Cancel button), or carry out some other command at a click of the mouse.

To create a command button, use the CommandButton tool in the Toolbox. A command button is a CommandButton object that includes many of the common control properties mentioned earlier, as well as the following design-time properties (among others):

Cancel-If this property is set to True, the button is selected when the user presses Esc. Note that you can have only one cancel button on a form.
Caption-Returns or sets the text that appears on the button face.
Default-If this property is set to True, the button is selected when the user presses Enter. Also, the button is displayed with a thin black border. You can have only one default button on a form.

Labels

You use labels to add text to the form. To create labels, use the Label button in the Toolbox to draw the label object, and then edit the Caption property. Although labels are mostly used to display text, you can also use them to name controls that don't have their own captions- such as text boxes, list boxes, scroll bars, and spinners.

It's even possible to define an accelerator key for the label and have that key select another control. For example, suppose you want to use a label to describe a text box, but you also want to define an accelerator key that the user can press to select the text box. The trick is that you must first create a label and set its Accelerator property. You then create the text box immediately after. Because the text box follows the label in the tab order, the label's accelerator key selects the text box.

Tip: To assign a label and accelerator key to an existing control, add the label and then adjust the tab order so that the label comes immediately before the control in the tab order.

Text Boxes

Text boxes are versatile controls that let the user enter text, numbers, and, in Excel, cell references and formulas. To create a text box, use the TextBox button in the Toolbox. Here are a few useful properties of the TextBox object:

EnterFieldBehavior-Determines what happens when the user tabs into the text box. If you select 0 (fmEnterFieldBehaviorSelectAll), the text within the field is selected. If you select 1 (fmEnterFieldBehaviorRecallSelect), only the text that the user selected the last time he was in the field is selected; the first time the user enters the field, the cursor is placed at the end of the text.
EnterKeyBehavior-When set to True, this property lets the user start a new line within the text box by pressing Enter. (Note that this is applicable only if you set MultiLine to True, as described in a moment.) When this property is False, pressing Enter moves the user to the next field.
MaxLength-This property determines the maximum number of characters that the user can enter.
MultiLine-Set this property to True to let the user enter multiple lines of text. If you've set EnterKeyBehavior to False, the user can start a new line of text by pressing Ctrl+Enter.
PasswordChar-If this property is set to True, the text box displays the user's entry as asterisks (which is useful if you're using the text box to get a password or other sensitive data).
Text-Returns or sets the text inside the text box.
WordWrap-When this property is True, the text box wraps to a new line when the user's typing reaches the right edge of the text box.

Frames

You use frames to create groups of two or more controls. There are three situations in which frames come in handy:

To organize a set of controls into a logical grouping-Let's say your form contains controls for setting program options and obtaining user information. You could help the user make sense of the form by creating two frames: one to hold all the controls for the program options, and one to hold the controls for the user information.
To move a set of controls as a unit-When you draw controls inside a frame, these controls are considered to be part of the frame object. Therefore, when you move the frame, the controls move right along with it. This can make it easier to rearrange multiple controls on a form.
To organize option buttons-If you enter multiple option buttons inside a frame, VBA treats them as a group and therefore allows the user to activate only one of the options.

To create a frame, click the Frame button in the Toolbox and then click and drag a box inside the form. Note that you use the Frame object's Caption property to change the caption that appears at the top of the box.

Option Buttons

Option buttons are controls that usually appear in groups of two or more; the user can select only one of the options. To create an option button, use the OptionButton tool. You can determine whether an option button starts off activated or deactivated by setting the Value property: If it's True, the option is activated; if it's False, the option is deactivated.

For option buttons to work effectively, you need to group them so that the user can select only one of the options at a time. VBA gives you three ways to do this:

Create a frame and then draw the option buttons inside the frame.
Use the same GroupName property for the options you want to group.
If you don't draw the option buttons inside a frame or use the GroupName property, VBA treats all the option buttons in a form as one group.

If you already have one or more "unframed"option buttons on your form, you can still insert them into a frame. Just select the buttons, cut them to the Clipboard, select the frame, and paste.VBA then adds the buttons to the frame.

Check Boxes

Check boxes let you include options that the user can toggle on or off. To create a check box, click the CheckBox button in the Toolbox.

As with option buttons, you can control whether a check box is initially activated (checked). Set its Value property to True to activate the check box, or to False to deactivate it.

Toggle Buttons

A toggle button is a cross between a check box and a command button: Click it once, and the button stays pressed; click it again, and the button returns to the unpressed state. You create toggle buttons by using the ToggleButton tool in the Toolbox.

You control whether a toggle button is initially activated (pressed) by setting its Value property to True to press the button or to False to "unpress" the button.

List Boxes

VBA offers two different list objects you can use to present the user with a list of choices: a ListBox and a ComboBox.

The ListBox Object

The ListBox object is a simple list of items from which the user selects an item or items. Use the ListBox button to create a list box. Here are some ListBox object properties to note:

ColumnCount-The number of columns in the list box.
ColumnHeads-If this property is True, the list columns are displayed with headings.
MultiSelect-Set this property to fmMultiSelectSingle to enable the user to select only one item in the list; if you set this property to fmMultiSelectMulti, the user may select multiple items in the list by clicking the items; if you set this property to fmMultiSelectExtended, the user may select multiple items in the list by holding down Ctrl and clicking the items. (For the multiple selection options, note that clicking or Ctrl+clicking are toggles that select and deselect items.)
RowSource-Determines the items that appear in the list. In Excel, enter a range or a range name.
Text-Sets or returns the selected item. For example, if the list box contains a list of months and you want August to be selected at startup, then you'd include the following code in the form's Initialize event:

ListBox1.Text = "August"

The ComboBox Object

The ComboBox object is a control that combines a text box with a list box. The user either enters an item in the text box or clicks the drop-down arrow to display the list box and then selects an item from the list. Use the ComboBox button to create this control.

Because the ComboBox is actually two separate controls, the available properties are an amalgam of those discussed earlier for a text box and a list box. You can also work with the following properties that are unique to a ComboBox object:

ListRows-Determines the number of items that appear when the user drops the list down.
MatchRequired-If this property is True, the user can enter only values from the list. If it's False, the user can enter new values.
Style-Determines the type of ComboBox. Use 0 (fmStyleDropDownCombo) for a list that includes a text box; use 2 (fmStyleDropDownList) for a list only.

List Box Techniques

How do you specify the contents of a list if the RowSource property isn't applicable (that is, if you're not working in Excel or if the data you want in the list isn't part of an Excel range)? In this case, you must build the list at runtime. You can use the AddItem method, described later in this section, or you can set the List property. For the latter, you must specify an array of values. For example, the following statements use a form's Initialize event to populate a list box with the days of the week:

Private Sub UserForm_Initialize()
    ListBox1.List() = Array("Monday", "Tuesday", "Wednesday",
"Thursday"," "Friday", "Saturday", "Sunday")
End Sub

List boxes also have a few useful methods for controlling from your VBA code the items that appear in a list box:

AddItem-Adds an item to the specified list box. Here's the syntax:
object.AddItem(text,index)
object
The name of the ListBox object to which you want to add the item.

text
The item's text.

index
The new item's position in the list. If you omit this argument, VBA adds the item to the end of the list.
Clear-Removes all the items from the specified list box.
RemoveItem-Removes an item from the specified list box using the following syntax:
object.RemoveItem(index)
object
The ListBox object from which you want to remove the item.

index
The index number of the item you want to remove.

Scrollbars

Scrollbars are normally used to navigate windows, but by themselves you can use them to enter values between a predefined maximum and minimum. Use the ScrollBar button to create either a vertical or horizontal scrollbar. Here's a rundown of the ScrollBar object properties you'll use most often in your VBA code:

LargeChange-Returns or sets the amount that the scrollbar value changes when the user clicks between the scroll box and one of the scroll arrows.
Max-Returns or sets the maximum value of the scrollbar.
Min-Returns or sets the minimum value of the scrollbar.
SmallChange-Returns or sets the amount that the scrollbar value changes when the user clicks one of the scroll arrows.
Value-Returns or sets the current value of the scrollbar.

Spin Buttons

A spin button is similar to a scrollbar in that the user can click the button's arrows to increment or decrement a value. To create a spin button, use the SpinButton tool in the Toolbox. The properties for a SpinButton object are the same as those for a ScrollBar (except that there is no LargeChange property).

Most spin buttons have a text box control beside them to give the user the choice of entering the number directly or selecting the number by using the spin button arrows. You have to use VBA code to make sure that the values in the text box and the spinner stay in sync. (In other words, if you increment the spinner, the value shown in the text box increments as well, and vice versa.)

To do this, you have to add event handler code for both controls. For example, suppose you have a text box named TextBox1 and a spin button named SpinButton1. Listing shows the basic event handler code that keeps the values of these two controls synchronized.

Event Handler Code That Keeps a Text Box and a Spin Button in Sync

Private Sub TextBox1_Change()
    SpinButton1.Value = TextBox1.Value
End Sub
 
Private Sub SpinButton1_Change()
    TextBox1.Value = SpinButton1.Value
End Sub

Tab Strips and MultiPage Controls

You can use frames to group related controls visually and help the user make sense of the form. However, there are two situations in which a frame falls down on the job.

The first situation is when you need the form to show multiple sets of the same (or similar) data. For example, suppose you have a form that shows values for sales and expense categories. You might want the form to be capable of showing separate data for various company divisions. One solution would be to create separate frames for each division and populate each frame with the same controls, but this is clearly inefficient. A second solution would be to use a list or a set of option buttons. This will work, but it might not be obvious to the user how he is supposed to display different sets of data, and these extra controls just serve to clutter the frame. A better solution is to create a tabbed form where each tab represents a different set of data.

The second situation is when you have a lot of controls. In this case, even the judicious use of frames won't be enough to keep your form from becoming difficult to navigate and understand. In situations where you have a large number of controls, you're better off creating a tabbed form that spreads the controls over several tabs.

In both of these situations, the tabbed form solution acts much like the tabbed dialog boxes you work with in Windows, Office, and other modern programs. To create tabs in your forms, VBA offers two controls: TabStrip and MultiPage.

The TabStrip Control

The TabStrip is an ideal way to give the user an intuitive method of displaying multiple sets of data. The basic idea behind the TabStrip control is that as the user navigates from tab to tab, the visible controls remain the same, and only the data displayed inside each control changes. The advantage here is that you need to create only a single set of controls on the form, and you use code to adjust the contents of these controls.

You create a TabStrip by clicking the TabStrip button in the Toolbox and then clicking and dragging the mouse until the strip is the size and shape you want. Here are a few points to keep in mind:

The best way to set up a TabStrip is to add it as the first control on the form and then add the other controls inside the TabStrip.
If you already have controls defined on the form, draw the TabStrip over the controls and then use the Send to Back command on the UserForm toolbar (or press Ctrl+K) to send the TabStrip to the bottom of the Z-order.
You can also display a series of buttons instead of tabs. To use this format, select the TabStrip and change the Style property to fmTabStyleButtons (or 1).

The MultiPage Control

The MultiPage control is similar to a TabStrip in that it displays a series of tabs along the top of the form. The major difference, however, is that each tab represents a separate form (called a page). Therefore, you use a MultiPage control whenever you want to display a different set of controls each time the user clicks a tab.

You add a MultiPage control to your form by clicking the MultiPage button in the Toolbox and then clicking and dragging the mouse until the control is the size and shape you want. It's important to remember that each page in the control is a separate object (a Page object). So each time you select a page, the values that appear in the Properties window apply only to the selected page. For example, the Caption property determines the text that appears in the page's tab. Also, you set up a page by selecting it and then drawing controls inside the page. (If you have controls on the form already, you can put them inside a page by cutting them to the Clipboard, selecting the page, and pasting the controls.)

Working with a MultiPage control in code is very similar to working with a TabStrip:

The Pages collection represents all the pages inside a MultiPage control. You refer to individual pages by their index numbers.
Use the Pages.Add method to add more pages to the control.
When the user selects a different tab, the MultiPage control's Change event fires.

[Previous] [Contents] [Next]