Creating a User Interface with Form Controls

Making your clients run your macro with shortcut keys, such as Ctrl+Shift+R, can be a satisfactory solution if you have only one macro in your pivot table report. However, suppose you want to allow your clients to perform several macro actions. You will want to give your clients a clear and easy way to run each macro without having to remember a gaggle of shortcut keys. A basic user interface provides the perfect solution. You can think of a user interface as a set of controls such as buttons, scrollbars, and other devices that allow a user to run macros with a simple click of the mouse.

It just so happens that Excel offers a set of controls designed specifically for creating user interfaces directly on a spreadsheet. These controls are called form controls. The general idea behind form controls is that you can place one on a spreadsheet and then assign a macro itmeaning a macro you have already recorded. After a macro is assigned to the control, that macro is executed, or played, when the control is clicked. Form controls can be found on the Forms toolbar. To get to the Forms toolbar, go up to the application menu and select View, Toolbars, Forms.

In the example shown in Figure 11.3, you want to allow your clients to refresh the pivot table with a click of a button. In this case, you can assign the RefreshData macro you recorded earlier to a command button from the Forms toolbar.

Figure 11.3. Open the Forms toolbar and select the command button control. Move your cursor onto the spreadsheet and left-click.

After you drop the command button control onto your spreadsheet, the Assign Macro dialog box, shown in Figure 11.4, will open and ask you to assign a macro to this button. Select the macro you want to assign to the button and then click OK.

Figure 11.4. Select the macro you want to assign to the button and then click OK. In this case, you want to select RefreshData.

Figure 11.5 shows your Refresh Pivot Table button ready to go. You will also see a couple other buttons to demonstrate that if you have multiple macros in a workbook, you can assign each macro to a different control. Keep in mind that all the controls in the Forms toolbar work in the same way as the command button, in that you assign a macro to run when the control is selected.

Figure 11.5. If you have multiple macros, you can assign each one to a different form control and then name the controls to distinguish between them.

When you have all the controls you need for your pivot table report, you can format the controls and surrounding spreadsheet to create a basic interface. Figure 11.6 shows your pivot table report after it has been formatted to give your users the feeling of a graphical interface.

Figure 11.6. You can easily create the feeling of an interface with a handful of macros, a few form controls, and a little formatting.

Recording Macros in Excel 2002 and Later Versions

Be aware that if you distribute a pivot table report with macros that were recorded in Excel 2002 or later, your interface may not work properly for someone using Excel 2000. This is because, after 2000, Microsoft introduced new objects and parameters to Excel VBA that are not recognized by Excel 2000.

For example, if you record a macro to refresh a pivot table in Excel 2003, Excel will generate the following code:


The same macro recorded in Excel 2000, will generate this code:


Because the Object and Method combination of PivotCache.Refresh does not exist in Excel 2000, the macro generated with Excel 2003 will fail.

There are only three ways around this problem:

  • Create your macros using Excel 2000. (Excel 2000 code will run fine in later versions of Excel.)

  • Make Excel 2002, or above, a requirement for using your pivot table reports.

  • Edit your macros manually to make them compatible with Excel 2000.

    Pivot Table Data Crunching
    Pivot Table Data Crunching for Microsoft Office Excel 2007
    ISBN: 0789736012
    EAN: 2147483647
    Year: 2003
    Pages: 140

    Similar book on Amazon © 2008-2017.
    If you may any questions please contact us: