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.
Table 6-1 ACTIVEX CONTROLS VERSUS EXCEL CONTROLS
Macro code storage
Respond to events
97, 2000, 2002, 2003 Control Toolbox
CheckBox, TextBox, CommandButton, OptionButton, ListBox, ComboBox, ToggleButton, SpinButton, ScrollBar, Label, Image (and others can be added) Spinner
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)
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?