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.