Determining the Most Appropriate User Interface


Determining the Most Appropriate User Interface

When you develop spreadsheets that others will use, you need to pay special attention to the user interface. By user interface, I mean the method by which the user interacts with the application and executes your VBA macros.

Excel 2007 makes some of these decisions irrelevant. Custom menus and toolbars are, for all intents and purposes, obsolete. This means that developers must learn how to work with the Ribbon.

Excel provides several features that are relevant to user interface design:

  • Ribbon customizations

  • Shortcut menu customization

  • Shortcut keys

  • Custom dialog boxes (UserForms)

  • Controls (such as a ListBox or a CommandButton) placed directly on a worksheet

I discuss these features briefly in the following sections and cover them more thoroughly in later chapters.

Customizing the Ribbon

The new Ribbon UI in Excel 2007 is a dramatic shift in user interface design. Fortunately, the developer has a fair amount of control over the Ribbon. Unfortunately, modifying the Ribbon isn't a simple task.

CROSS-REFERENCE  

See Chapter 22 for information about working with the Ribbon.

image from book
Menu and Toolbar Compatibility

Excel 2007 still supports custom menus and toolbars, but the way these UI elements are handled may not be to your liking.

The following figure shows a custom menu and toolbar displayed in Excel 2003. The menu and toolbar were created using my Power Utility Pak add-in. Each menu item and toolbar button executes a macro.

image from book

As shown in the following figure, when the Power Utility Pak add-in is installed in Excel 2007, the custom menu appears in a group labeled Add-Ins image from book Menu Commands, and the custom toolbar is in a group labeled Add-Ins image from book Custom Toolbars (the toolbars cannot be moved or resized). These Ribbon groups display the menu additions and toolbars for all the applications or add-ins that are loaded. The menu items and toolbar buttons still function, but the designer's original UI conception has been compromised.

image from book
image from book
 

Customizing shortcut menus

Excel 2007 still allows the VBA developer to customize the right-click shortcut menus. Figure 6-1 shows a customized shortcut menu that appears when you right-click a row number. Notice that this shortcut menu has six menu items (at the bottom) that aren't normally available.

image from book
Figure 6-1: An example of a customized shortcut menu.
CROSS-REFERENCE  

Chapter 23 describes how to work with shortcut menus using VBA.

Creating shortcut keys

Another user interface option at your disposal is to create custom shortcut keys. Excel lets you assign a Ctrl key (or Shift+Ctrl key) combination (shortcut) to a macro. When the user presses the key combination, the macro executes.

Be aware, however, of these two caveats: First, you must make it clear to the user which keys are active and what they do; second, you need to be careful not to 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 6-2 shows an example of a custom dialog box.

image from book
Figure 6-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. You create and edit UserForms in the VBE. 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.

CROSS-REFERENCE  

I cover UserForms in detail in Part IV.

Using ActiveX controls on a worksheet

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-3 shows a simple worksheet model with several UserForm controls inserted directly on 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 .

image from book
Figure 6-3: You can add dialog box controls to worksheets and link them to cells.
CD-ROM  

This workbook is available on the companion CD-ROM. The file is named image from book  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. This lets the user make choices by operating familiar controls rather than making entries into cells.

Access these controls by using the Developer image from book Controls image from book Insert command (see Figure 6-4).

image from book
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
Open table as spreadsheet
 

ActiveX Controls

Form Controls

Excel versions

97, 2000, 2002, 2003, 2007

5, 95, 97, 2000, 2002, 2003, 2007

Controls available

CheckBox, TextBox, CommandButton, OptionButton, ListBox, ComboBox, ToggleButton, SpinButton, ScrollBar, Label, Image (and others can be added)

Label, 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 that you'll take to meet those needs, and decide on the components that you'll use for the user interface, it's time to get down to the nitty-gritty and start creating the application. This step, of course, comprises a great deal of the total time that you spend on a particular project.

How you go about developing the application depends on your own personal style and the nature of the application. Except for simple fill-in-the-blanks template workbooks, your application will probably use macros. Developing the macros is the tough part. It's easy to create macros in Excel, but it's difficult to create good macros.




Excel 2007 Power Programming with VBA
Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
ISBN: 0470044012
EAN: 2147483647
Year: 2007
Pages: 319

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net