Providing Access to an Applications Features

I recently created an Excel application for a well-known fast food company. The company wanted to send out data on sales and other things to its field offices, in the form of a rather complicated Excel pivot table. They wanted the field personnel to be able to filter the pivot table by various means (thus creating smaller pivot tables) as well as generate a variety of charts showing different views of the data. (The complete application involved other features, but this will illustrate the point.)

In particular, the main pivot table contains several types of data (sales, transaction counts, and so on) for several Designated Marketing Areas (DMAs) and store types (company, franchise, or both). One feature of the application is a chart-creating utility for this data. But where should the code for this feature go and how should the field personnel be given access to this charting utility?

Since the charting utility directly involves the pivot table, it seems reasonable in this case to simply place a command button labeled Make Chart(s) directly on the pivot table worksheet.

When the user clicks the button, a dialog box such as the one shown in Figure 10-1 appears, allowing the user to make various selections and then create the chart or charts.

Figure 10-1. Dialog for a charting utility

Figure 10-1. Dialog for a charting utility

In general, there are several possible options for providing access to the charting utility, that is, for displaying the dialog box in Figure 10-1 (or, for that matter, for providing access to any macro):

• Select it from the Macro dialog by choosing Tools Macro Macros. The Macro dialog was discussed in Chapter 4. This is the most efficient method for a user who writes macros and wants to run one quickly (and it provides an easy method to run many of the very short examples presented in this book). But since the dialog displays only the names of macros to be run, it's not suitable for a user who is unfamiliar with the macros, nor is it a very efficient method of running frequently used macros.

• Run or display it automatically when a workbook opens by attaching code to one of Excel's events, in this case the Open event. Events are discussed in detail in Chapter 11.

• Place a button directly on the worksheet.

• Place a button on an existing Excel toolbar. This can be done programmatically (a topic discussed in Chapter 12) or through the user interface (see Section 10.1.2 later in this section).

• Create a new toolbar and add the button to it, either programmatically or through the user interface. For information on the latter, see Section 10.1.1 later in this section.

• Add a menu item to an existing Excel menu, either programmatically or through the user interface.

• Create a new menu bar and add a menu item, either programmatically or through the user interface.

In this case, since we did not want the user to be able to invoke the chart-printing utility unless the worksheet containing the pivot table was active, we opted for the button on the worksheet approach. This is not to say, however, that the other approaches would not work.

On the other hand, if the utility in question has wider applicability, then it would probably make more sense to use a toolbar or add a menu item. (I much prefer menu items over toolbar buttons, because they are easily invoked using the keyboard and don't get in the way of other windows.)

Indeed, an application that has many features might benefit from a dedicated toolbar or menu bar or a dedicated popup menu on, say, the main Excel worksheet and chart menu bars.

In short, the decision as to how to provide access to the features of an Office application depends on several things, including the complexity of the application, the scope of its features, and personal preferences.

0 0

Post a comment