Controlling the Report with Macros The users of your PivotTable and PivotChart will use the map to change states. You'll need to create a macro that both changes the state and adjusts the width of the chart to make it match the PivotTable grid. In addition, you'll need to create a macro that refreshes the PivotTable so that new data appears as changes are made to the database. Make a Macro Change the PivotTable If you create a macro that takes an argument giving the state code, you can use that one macro for any state. First create a macro that changes the PivotTable to display orders for an arbitrary state, and then add the argument to generalize the macro. Run Macro  -
On the Visual Basic toolbar, click the Run Macro button. Type SetPivot as the macro name, and click Create. The Visual Basic Editor opens with a new macro named SetPivot. -
Enter these statements as the body of the SetPivot macro: Worksheets("Data").Select ActiveSheet.PivotTables(1).PageFields(1).CurrentPage = "OR" Worksheets("ChartData").PivotTables(1).PageFields(1).CurrentPage = "OR" This macro changes the PivotTable to display the orders for Oregon. The first statement selects the Data sheet, just in case it wasn't already the active sheet. The second statement changes the value of the CurrentPage property for the one page field of the one PivotTable on the sheet. The third statement makes the same change to the one PivotTable on the Chart sheet. For now, the new state (OR for Oregon) is a constant. -
Press F5 to run the macro, which now changes the state to Oregon. Change the state code in the macro (in both places) to NV (for Nevada), and try it again. -
Next you can change the macro to accept an argument for the state code. Insert NewState between the parentheses after the name of the macro. Then replace both occurrences of NV with NewState. -
Press Ctrl+G to display the Immediate window, and in that window, type SetPivot 'ID' and then press the Enter key. The state changes to Idaho. The SetPivot macro is ready to change both the PivotTable and the chart to any state. -
Save the Chapter12 workbook. A state with few orders, such as Idaho, has narrower labels than some of the larger states. The macro needs to adjust the horizontal position of the chart whenever you or another user changes the state. Make a Macro Adjust a Chart A stepped area chart looks best if it doesn't have borders around each area. Unfortunately, even if you remove the borders, each time you change the PivotTable, the borders reappear. Fortunately, you can write a simple macro to remove the borders each time the PivotTable changes. Also, the size of the value labels affects how the chart label gridlines align with the PivotTable gridlines. That's also something you can fix with a macro whenever the state changes. -
Activate the Visual Basic Editor, and click below the SetPivot macro. From the Insert menu, click File, change to the folder containing the practice files for this book, and double-click the Code12a file. This AdjustChart macro appears in the module: Sub AdjustChart() Dim myObject As ChartObject Dim myChart As Chart Dim myWidth As(?) Dim myLeft As(?) Dim mySeries As Series Set myObject = ActiveSheet.ChartObjects(1) Set myChart = myObject.Chart myWidth = myChart.PlotArea.Width _ - myChart.PlotArea.InsideWidth myWidth = myChart.ChartArea.Left _ + myChart.PlotArea.Left + myWidth myWidth = myWidth - 0.5 myLeft = Range("C1").Left - myWidth myObject.Left = myLeft myObject.Width = Range("L1").Left - myLeft For Each mySeries In myChart.SeriesCollection mySeries.Border.LineStyle = xlNone Next mySeries End Sub This macro consists of four parts, separated by blank lines. The first part declares some variables and assigns references to both the chart's container object and the chart itself. The second part of the macro calculates the width of the area from the left edge of the chart to the left edge of the plot area rectangle. A plot area on a chart has two definitions. The plot area you select on the chart is actually the inside plot area. The outer plot area includes the area with the axis labels. By subtracting the inside width of the plot area from the width, you get the size of the labels. Adding that to the starting position of the chart area and the outer plot area gives you the total width from the edge of the chart container to the edge of the inner plot area. Subtracting a half point from the width makes the grid line up better. On a PivotChart, you can't adjust the positions of any of the objects within the chart. You can adjust only the location of the container. The third part of the macro calculates how much the chart needs to be shifted to the right and then subtracts that same amount from the width to keep the right side aligned. The fourth and final part of the macro removes the border lines from each of the series in the chart. The On Error Resume Next statement is there because some of the charts contain empty series-series that are technically in the SeriesCollection collection but don't appear on the screen-and can't be modified. -
Add the statement AdjustChart to the end of the SetPivot macro, and in the Immediate window, execute the statement that changes the report to Idaho. -
Insert the statement Application.ScreenUpdating = False at the top of the SetPivot macro, and change the PivotTable to 'WA'. -
Save the Chapter12 workbook. The SetPivot macro is ready to use. Later you'll create a graphical interface that calls the SetPivot macro, passing the appropriate state code as an argument. You've now created the core functionality of the application. The PivotTable retrieves the data from the external database, and the chart presents the data in a visually appealing way. Your next task is to create an effective mechanism for interacting with the application. |
