Using ActiveX controls on a worksheet

Excel also lets you add the UserForm ActiveX controls to a worksheet's drawing layer (an invisible layer on top of a sheet that holds pictures, charts, and other objects). Figure 6-1 shows a simple worksheet model with several UserForm controls inserted directly on the worksheet. This sheet contains some examples of an OptionButton, a ScrollBar, a CommandButton, and a CheckBox.

This workbook, which also includes a few simple macros,is available on the companion CD-ROM.

Perhaps the most common control is a CommandButton. By itself, a Command-Button doesn't do anything, so you have to attach a macro to each CommandButton.

Using dialog box controls directly in a worksheet often eliminates the need for custom dialog boxes. You can often greatly simplify the operation of a spreadsheet by adding a few ActiveX controls to a worksheet. This lets the user make choices by operating familiar controls rather than making entries into cells.

The ActiveX controls are found on the Control Toolbox toolbar. You can also use Excel 5/95 compatible controls on a worksheet. These controls, which are not ActiveX controls, are available on the Forms toolbar. These controls are not discussed in this book. Table 6-1 summarizes these two classes of controls.

Figure 6-1: Directly adding UserForm controls may make a worksheet easier to use.


Excel versions

Which toolbar?

Controls available

Macro code storage

Macro name


Respond to events

ActiveX Controls

97, 2000, 2002, 2003 Control Toolbox

CheckBox, TextBox, CommandButton, OptionButton, ListBox, ComboBox, ToggleButton, SpinButton, ScrollBar, Label, Image (and others can be added) Spinner

Excel Controls

Label, GroupBox, Button, CheckBox, OptionButton, ListBox, DropDown (ComboBox), ScrollBar,

In the code module for the Sheet

Corresponds to the control name (for example, CommandButton1_Click)

UserForm controls

Extensive, using the Properties box Yes

In any standard VBA module

Any name you specify Dialog Sheet controls


Click or Change events only

Was this article helpful?

0 0

Post a comment