Chapter 42: Using UserForm Controls in a Worksheet

FIGURE 42.2

Excel’s two sets of worksheet controls.

TABLE 42.1

	ActiveX Controls
Button	What It Does
CommandButton	Inserts a CommandButton control (a clickable button)
ComboBox	Inserts a ComboBox control (a drop-down list)
CheckBox	Inserts a CheckBox control (to control Boolean options)
ListBox	Inserts a ListBox control (to allow the user to select an item from a list)
TextBox	Inserts a TextBox control (allows the user to type text)
ScrollBar	Inserts a ScrollBar control (to specify a value by dragging a bar)
SpinButton	Inserts a SpinButton control (to specify a value by clicking up or down)
OptionButton	Inserts an OptionButton control (to allow a user to select from multiple options)
Label	Inserts a Label control (a container for text)
Image	Inserts an Image control (to hold an image)
ToggleButton	Inserts a ToggleButton control (to control Boolean options)
More Controls	Displays a list of other ActiveX controls that are installed on your system. Not all
	these controls work with Excel.

Using Controls

Adding ActiveX controls in a worksheet is easy, but you need to learn a few basic facts about how to use them.

861

Part VI: Programming Excel with VBA

Adding a control

To add a control to a worksheet, choose Developer Controls Insert. From the Insert dropdown icon list, click the control that you want to use and then drag in the worksheet to create the control. You don’t need to be too concerned about the exact size or position because you can modify those properties at any time.

Warning

Make sure that you select a control from the ActiveX controls — not from the Forms controls. If you insert a Forms control, the instructions in this chapter will not apply. n

About design mode

When you add a control to a worksheet, Excel goes into design mode. In this mode, you can adjust the properties of any controls on your worksheet, add or edit macros for the control, or change the control’s size or position.

Note

When Excel is in design mode, the Design Mode icon in the Developer Controls section appears highlighted. You can click this icon to toggle design mode on and off. n

When Excel is in design mode, the controls aren’t enabled. To test the controls, you must exit design mode by clicking the Design Mode icon. When you’re working with controls, you’ll probably need to need to switch in and out of design mode frequently.

Adjusting properties

Every control that you add has various properties that determine how it looks and behaves. You can adjust these properties only when Excel is in design mode. When you add a control to a worksheet, Excel enters design mode automatically. If you need to change a control after you exit design mode, click the Design Mode icon in the Controls section of the Developer tab.

To change the properties for a control

1.Make sure that Excel is in design mode.

2.Click the control to select it.

3.If the Properties window isn’t visible, click the Properties icon in the Controls section of the Developer tab. The Properties window appears, as shown in Figure 42.3.

4.Select the property and make the change.

862

Chapter 42: Using UserForm Controls in a Worksheet

The manner in which you change a property depends upon the property. Some properties display a drop-down list from which you can select from a list of options. Others (such as Font) provide a button that when clicked, displays a dialog box. Other properties require you to type the property value. When you change a property, the change takes effect immediately.

Tip

To find out about a particular property, select the property in the Properties window and press F1. n

The Properties window has two tabs. The Alphabetic tab displays the properties in alphabetical order. The Categorized tab displays the properties by category. Both tabs show the same properties; only the order is different.

FIGURE 42.3

Use the Properties window to adjust the properties of a control — in this case, a CommandButton control.

Common properties

Each control has its own unique set of properties. However, many controls share properties. This section describes some properties that are common to all or many controls, as set forth in Table 42.2.

Note

Some ActiveX control properties are required (for example, the Name property). In other words, you can’t leave the property empty. If a required property is missing, Excel will always tell you by displaying an error message. n

863

Part VI: Programming Excel with VBA

TABLE 42.2

	Properties Shared by Multiple Controls
Property	Description
AutoSize	If True, the control resizes itself automatically, based on the text in its caption.
BackColor	The background color of the control.
BackStyle	The style of the background (either transparent or opaque).
Caption	The text that appears on the control.
LinkedCell	A worksheet cell that contains the current value of a control.
ListFillRange	A worksheet range that contains items displayed in a ListBox or ComboBox control.
Value	The control’s value.
Left and Top	Values that determine the control’s position.
Width and Height	Values that determine the control’s width and height.
Visible	If False, the control is hidden.
Name	The name of the control. By default, a control’s name is based on the control type.
	You can change the name to any valid name. However, each control’s name must
	be unique on the worksheet.
Picture	Enables you to specify a graphic image to display.

Linking controls to cells

Often, you can use ActiveX controls in a worksheet without using any macros. Many controls have a LinkedCell property, which specifies a worksheet cell that is “linked” to the control.

For example, you may add a SpinButton control and specify cell B1 as its LinkedCell property. After doing so, cell B1 contains the value of the SpinButton, and clicking the SpinButton changes the value in cell B1. You can, of course, use the value contained in the linked cell in your formulas.

Note

When specifying the LinkedCell property in the Properties window, you can’t “point” to the linked cell in the worksheet. You must type the cell address or its name (if it has one). n

Creating macros for controls

To create a macro for a control, you must use the Visual Basic Editor (VB Editor). The macros are stored in the code module for the sheet that contains the control. For example, if you place an

864

Chapter 42: Using UserForm Controls in a Worksheet

ActiveX control on Sheet2, the VBA code for that control is stored in the Sheet2 code module. Each control can have a macro to handle any of its events. For example, a CommandButton control can have a macro for its Click event, its DblClick event, and various other events.

Tip

The easiest way to access the code module for a control is to double-click the control while in design mode. Excel displays the VB Editor and creates an empty procedure for the control’s default event. For example, the default event for a CheckBox control is the Click event. Figure 42.4 shows the autogenerated code for a control named CheckBox1, located on Sheet1. n

FIGURE 42.4

Double-clicking a control in design mode activates the VB Editor and enters an empty event-handler procedure.

The control’s name appears in the upper-left portion of the code window, and the event appears in the upper-right area. If you want to create a macro that executes when a different event occurs, select the event from the list in the upper-right area.

The following steps demonstrate how to insert a CommandButton and create a simple macro that displays a message when the button is clicked:

1.Choose Developer Controls Insert.

2.Click the CommandButton tool in the ActiveX Controls section.

3.Click and drag in the worksheet to create the button. Excel automatically enters design mode.

865