Excel 2019 Power Programming with VBA. Michael Alexander

play a major role in the user interfaces that you design for your applications. Figure 1.2 shows an example of a custom dialog box.

FIGURE 1.2 A dialog box created with Excel's UserForm feature

      A custom dialog box is known as a UserForm. A UserForm can solicit user input, get a user's options or preferences, and direct the flow of your entire application. The elements that make up a UserForm (buttons, drop-down lists, check boxes, and so on) are called controls—more specifically, ActiveX controls. Excel provides a standard assortment of ActiveX controls, and you can also incorporate third-party controls.

      After adding a control to a dialog box, you can link it to a worksheet cell so that it doesn't require any macros (except a simple macro to display the dialog box). Linking a control to a cell is easy, but it's not always the best way to get user input from a dialog box. Most of the time, you want to develop VBA macros that work with your custom dialog boxes.

      

We cover UserForms in detail in Part III.

      Using ActiveX controls on a worksheet

Excel also lets you add 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 1.3 shows a simple worksheet model with several UserForm controls inserted directly in the worksheet. This sheet contains the following ActiveX controls: a CheckBox, a ScrollBar, and two sets of OptionButtons. This workbook uses no macros. Rather, the controls are linked to worksheet cells.

FIGURE 1.3 You can add UserForm controls to worksheets and link them to cells.

      ON THE WEB

      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

      Executing the development effort

      After you identify user needs, determine the approach you'll take to meet those needs, and decide on the components that you'll use for the user interface. Next, it's time to get down to the nitty-gritty and start creating the application. This step, of course, constitutes a great deal of the total time you spend on a particular project.

      How you go about developing the application depends on your personal style and the nature of the application. Except for simple fill-in-the-blanks template workbooks, your application will probably use macros. Creating macros in Excel is easy, but creating good macros is difficult.

Concerning Yourself with the End User

      In this section, we discuss the important development issues that surface as your application becomes more and more workable and as the time to package and distribute your work grows nearer.

      Testing the application

      How many times have you used a commercial software application, only to have it bomb out on you at a crucial moment? Most likely, the problem was caused by insufficient testing that didn't catch all of the bugs. All nontrivial software has bugs, but in the best software, the bugs are simply more obscure. As you'll see, you sometimes must work around the bugs in Excel to get your application to perform properly.