Using Controls On Worksheets

Category: General VBA / UserForms | [Item URL]

A companion file is available: Click here to download

Most developers know that it's possible to insert dialog box controls (such as buttons and checkboxes) directly on a worksheet. This topic, however, is the source of a great deal of confusion. This tip attempts to clarify some of the issues related to using controls on worksheets.

Two types of controls

First, it's important to understand that Excel supports two general types of controls:

• ActiveX Controls
• Excel Controls

These two types of controls are described in the table below.

	ActiveX Controls	Excel Controls
Excel versions	97, 2000	5, 95, 97, 2000
Which toolbar?	Control Toolbox	Forms
Controls available	CheckBox, TextBox, CommandButton, OptionButton, ListBox, ComboBox, ToggleButton, SpinButton, ScrollBar, Label, Image	Label, GroupBox, Button, CheckBox, OptionButton, ListBox, 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 (e.g., CommandButton1_Click)	Any name you specify.
Correspond to...	UserForm controls	Dialog Sheet controls
Customization	Extensive, using the Properties box	Minimal
Respond to events	Yes	Click or Change events only

Using ActiveX controls

To insert an ActiveX control on a worksheet, make sure the Control Toolbox toolbar is displayed. Then click the desired control, and drag in the sheet to create the control. After adding a control, Excel is in "design mode." To make any changes to the control, Excel must be in design mode. To test the control, Excel must not be in design mode. Use the Design Mode button on the Control Toolbox toolbar to toggle design mode.

To customize the control, right-click it and select Properties. This displays the Properties window. Each control has its own set of properties.

Some ActiveX controls can be connected to a cell (i.e., the LinkedCell property). Other controls can receive information from a range. For example, a ListBox control can get its list data from the ListFillRange property. Consult the online help for complete details regarding the properties for each control.

Each ActiveX control also triggers events. For example, a CommandButton control generates a Click event. You can write an event-handler Sub procedure to respond to the events. The procedure below, for example, displays a message box when a CommandButton (named CommandButton1) is clicked:

Private Sub CommandButton1_Click()
    MsgBox "CommandButton1 was clicked."
End Sub

Event-handler procedures must be located in the code module for the sheet on which the control is placed. It's important to understand that you cannot assign an arbitrary macro to an ActiveX control. To access the correct code module, right-click the control and choose View Code. The code module displays two drop-down controls at the top. Use these controls to determine the events supported for your control.

Using Excel controls

To insert an Excel control on a worksheet, make sure the Forms toolbar is displayed. Then click the desired control and drag in the sheet to create the control.

Right-click the control to access the Format dialog box. Any customizations to the control are made using this dialog box. Formatting options are limited. To assign a macro to the control, right-click it and select Assign Macro. Excel controls can execute any macros.