Microsoft Office Tutorials and References
In Depth Information
Determining the Most Appropriate User Interface
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. These ActiveX controls let 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 Figure 6-4). If the
Developer tab isn’t on the Ribbon, add it by using the Customize Ribbon tab of the Excel Options
dialog box.
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.
Table 6-1: ActiveX Controls Versus Form Controls
ActiveX Controls
Form Controls
Excel versions
97, 2000, 2002, 2003, 2007, 2010
5, 95, 97, 2000, 2002, 2003, 2007, 2010
Controls available
CheckBox, TextBox, CommandButton,
OptionButton, ListBox, ComboBox,
ToggleButton, SpinButton, ScrollBar,
Label, Image (and others can be
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
Search JabSto ::

Custom Search