Читать книгу Excel 2019 Power Programming with VBA - Michael Alexander, Dick Kusleika - Страница 46

This workbook is available on this book's website. The file is named worksheet controls.xlsx.

Perhaps the most common control is a CommandButton. By itself, a CommandButton doesn't do anything, so you need 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 (or form controls) to a worksheet. These ActiveX controls let the user make choices by operating familiar controls rather than making entries in cells.

Access these controls by using the Developer ➪ Controls ➪ Insert command (see Figure 1.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 1.4 Using the Ribbon to add controls to a worksheet

The controls come in two types: form controls and ActiveX controls. Both sets of controls have their advantages and disadvantages. Generally, form controls are easier to use, but ActiveX controls are a bit more flexible. Table 1.1 summarizes these two classes of controls.

TABLE 1.1 ActiveX Controls versus Form Controls

	ActiveX Controls	Form Controls
Excel versions	97, 2000, 2002, 2003, 2007, 2010, 2013, 2016, 2019	5, 95, 97, 2000, 2002, 2003, 2007, 2010, 2013, 2016, 2019
Controls available	CheckBox, TextBox, CommandButton, OptionButton, ListBox, ComboBox, ToggleButton, Spin Button, ScrollBar, Label, Image (and others can be added)	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
Customization	Extensive, using the Properties box	Minimal
Respond to events	Yes	Click or Change events only