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.

Search for Tips

All Tips

Browse Tips by Category

Tip Books

Needs tips? Here are two books, with nothing but tips:

Contains more than 100 useful tips and tricks for Excel 2013 | Other Excel 2013 books | Amazon link: 101 Excel 2013 Tips, Tricks & Timesavers

Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks

© Copyright 2018, J-Walk & Associates, Inc.
Privacy Policy