Using ActiveX controls on a worksheet

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

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-3 shows a simple worksheet model with several UserForm controls inserted directly on the worksheet. This sheet contains the following ActiveX controls: a Checkbox, a ScrollBar, and two sets of OptionButtons. This workbook uses no macros. Rather, the controls are linked to worksheet cells.

1 IE 11

Mnntihr riviwii S^,i77.ia

■wo MIrmr iwwh ««Jkf IB Pwrtnl L«i>h ffcJi fe) ft! W U-1**

Figure 6-3: You can add dialog box controls to worksheets and link them to cells.

CD- This workbook is available on the companion CD-ROM. The file is named % worksheet ROM controls.xlsx.

Perhaps the most common control is a CommandButton. By itself, a CommandButton doesn't do anything, so you need 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 (or Form controls) to a worksheet. This lets the user make choices by operating familiar controls rather than making entries into cells.

Access these controls by using the Developer Controls Insert command (see ).

Home In sen Page Li/out Formulai Data Re

3 Record Macro

fartr I

f^f Properties

Use Rdtttve Reference:


¡\ Macro S*£urity

Dsii^ri rj Run D ¿leg


Forni (oiitivb

1 - id

-i J R U j e



\Cj a*

a jb: _td H

: -

ActiveX Controls

M LS E -ft m j

41 ©

A ¿1 ? %

Figure 6-4: Worksheet controls.

Figure 6-4: Worksheet controls.

The controls come in two types: Form Controls and ActiveX Controls. Both sets of controls have their advantages and disadvantages. Generally, the Form controls are easier to use, but the ActiveX controls are a bit more flexible. Table 6-1 summarizes these two classes of controls.


+ Open table as spreadsheet

ActiveX Controls

Form Controls

Excel versions

97, 2000, 2002, 2003, 2007

5, 95, 97, 2000, 2002, 2003, 2007

Controls available

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

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

Macro code storage

In the code module for the Sheet

In any standard VBA module

Macro name

Corresponds to the control name (for example, CommandButton1 Click)

Any name you specify

Correspond to...

UserForm controls

Pre-Excel 97 Dialog Sheet controls


Extensive, using the Properties box


Respond to events


Click or Change events only

Was this article helpful?

0 -1

Post a comment