When you record a macro, Excel creates a module that stores the recorded steps of your actions. These recorded steps are actually lines of VBA code that make up your macro. You can add some interesting functionality to your pivot table reports by tweaking your macro's VBA code in order to achieve various effects.
To get a better understanding of how this works, start by creating a new macro that will extract the top five records by customer. Go up to the application menu and select Tools, Macro, Record New Macro. The dialog box shown in Figure 11.7 will be activated.
Figure 11.7. Name your new macro "GetTopNthCusts" and specify that you want to store the macro in This Workbook. Click OK to start recording.
After you have started recording, right-click on the Customer field and select Field Settings. Click the Advanced button and enter the settings you see in Figure 11.8. These settings will give you the top five customers by revenue.
Figure 11.8. In the Advance Options for the Customer field, enter the settings you see here to get the top five customers by revenue. Then click OK.
After successfully recording the steps to extract out the top five customers by revenue, go up to the application menu and select Tools, Macro, Stop Recording.
You now have a macro that, when played, will filter your pivot table to the top five customers by revenue. The plan is to tweak this macro to respond to a scrollbar. That is, you will force the macro to base the number used to filter the pivot table on the number represented by a scrollbar in your user interface. In other words, a user will be able to get the top five, top eight, or top 32, simply by moving a scrollbar up and down.
To get a scrollbar onto your spreadsheet, activate the Forms toolbar, select a scrollbar, and place it onto your spreadsheet. Unlike a command button, the scrollbar control requires some initial setup before you can use it. Right-click on the scrollbar and select Format Control. You will see the Format Object dialog box shown in Figure 11.9.
Figure 11.9. After you have placed a scrollbar on your spreadsheet, right-click on the scrollbar and select Format Control.
While you're in the Format Control dialog box, make the following setting changes: Set Minimum Level to 1 so the scrollbar cannot go below 1, set Maximum Level to 200 so the scrollbar cannot go above 200, and set the Cell Link to $K$2 so that the number represented by the scrollbar will output to cell K2. Click the OK button to apply to your changes.
Next, right-click on your newly formatted scrollbar and select Assign Macro to activate the dialog box shown in Figure 11.10. Assign the GetTopNthCusts macro you just recorded to your scrollbar. This will ensure that this macro will play each time the scrollbar is clicked.
Figure 11.10. Right-click on the scrollbar and select Assign Macro. Select the GetTopNthCusts macro from the list and then click OK.
At this point, test your scrollbar by clicking on it. When you click on your scrollbar, two things should happen: The GetTopNthCusts macro should play, and the number in cell K2 should change to reflect your scrollbar's position. The number in K2 is important because that is the number you are going to use to tie your macro to your scrollbar.
The only thing left to do now is to tweak your macro to respond to the number in cell K2, effectively tying it to your scrollbar. In order to do this, you will have to get to the VBA code that makes up the macro. There are several ways to get you there, but for the purposes of this example, select Tools, Macro, Macros. This will open up the Macro dialog box shown in Figure 11.11, exposing several options. From here, you can run, delete, step into, or edit a selected macro. To get to the VBA code that makes up your macro, select the macro and then click Edit.
Figure 11.11. To get to the VBA code that makes up the GetTopNthCusts macro, select the macro and then click Edit.
As you can see in Figure 11.12, the Visual Basic Editor will open up with a detailed view of all the VBA code that makes up this macro. Your goal here is to replace the hard-coded number 5, as specified when you originally recorded your macro, with the value in cell K2, which is tied to your scrollbar.
Figure 11.12. You will notice that the number 5 is hard-coded as part of your macro. This is because you originally recorded your macro to filter the top five customers by revenue.
You will delete the number 5 and replace it with the following:
Your macro's code should look similar to the code shown in Figure 11.13.
Figure 11.13. Simply delete the hard-coded number 5 and replace it with this reference to cell K2: ActiveSheet. Range("K2"). Value.
Close the Visual Basic Editor to get back to your pivot table report. Test your scrollbar by setting it to 11. Your macro should play and filter out the top 11 customers by revenue, as shown here in Figure 11.14.
Figure 11.14. After a little formatting, you have a clear and easy way for your clients to get the top customers by revenue.
CASE STUDY: Synchronize Two Pivot Tables with One Combo Box
The pivot table report in Figure 11.15 contains two pivot tables. Each pivot table has a page field for allowing you to select a market. The problem is that every time you select a market from the page field in one pivot table, you have to select the same market from the page field in the other pivot table to ensure you are analyzing the correct Units Sold versus Revenue.
Figure 11.15. With two pivot tables containing page fields that filter out a market, you will have to ensure that you synchronize both pivot tables when analyzing data for a particular market.
Not only is it a bit of a hassle to have to synchronize both pivot tables every time you want to analyze a new market's data, but there is a chance you, or your clients, may forget to do so.
One way to synchronize these pivot tables is to use a combo box. The idea is to record a macro that selects a market from the Market field of both tables. Then create a combo box and fill it with the market names that exist in your two pivot tables. Finally, alter your macro to filter both pivot tables, using the value from your combo box.
Here are the steps in performing this task:
Figure 11.23 shows your final result. You now have a user-friendly interface that allows your clients to select a market in both pivot tables simply by selecting the market in one combo box.
Figure 11.23. Your pivot table report is ready to use!