shortcut menus
Excel allows the VBA developer to customize the right-click shortcut menus. Right-click menus can offer users a way to trigger an action easily without having to move too far from the range in which they are working. Figure 1.1 illustrates a customized shortcut menu that appears when a cell is right-clicked.
FIGURE 1.1 A customized shortcut menu
Creating shortcut keys
Another user interface option at your disposal is a custom shortcut key. Excel lets you assign a Ctrl key (or Shift+Ctrl key) combination to a macro. When the user presses the key combination, the macro executes.
There are two caveats, however. First, make it clear to the user which keys are active and what they do. Second, do not assign a key combination that's already used for something else. A key combination that you assign to a macro takes precedence over the built-in shortcut keys. For example, Ctrl+S is a built-in Excel shortcut key used to save the current file. If you assign this key combination to a macro, you lose the capability to save the file with Ctrl+S. Remember that shortcut keys are case sensitive, so you can use a combination such as Ctrl+Shift+S.
Creating custom dialog boxes
Anyone who has used a personal computer for any length of time is undoubtedly familiar with dialog boxes. Consequently, custom Excel dialog boxes can 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.
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,
|
