Using Controls On Worksheets
A companion file is available: Click here to downloadMost 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.
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
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