Worksheet Events

As promised here are a couple of examples.



Example 1: a macro that rescales the chart axis.



We all know that sometimes Excel doesn't scale chart axes for maximum impact. Many a time I create a line chart where all the lines are squished together at the top of the chart because Excel insists on starting the axis at zero, even when this is not selected.



I have written a macro to identify when the pivot table that calculates my chart data updates, and scales my chart to the value in cell "MinAxis", which has a formula to calculate a more appropriate minimum value for my y axis.



The pseudocode for "MinAxis" is =20*FLOOR(IFERROR(MIN(values),0)*0.9/20,1)



Private Sub Worksheet_PivotTableUpdate(ByVal TargetPivotTable As PivotTable)

' Set lower limit of chart Y axis to the calculated figure.

ActiveSheet.ChartObjects("MyChart ").Activate

ActiveSheet.ChartObjects("MyChart").Chart.Axes(xlValue).MinimumScale = ActiveSheet.Range("MinAxis").Value

End Sub



Example 2: a macro to align selections in two different pivot tables.



I find that pivot tables based on different pivot caches can't both be controlled by the same slicer. This macro takes the selections in one pivot table and mirrors them in another, so the user can make selections in both pivot tables from one slicer.



Obviously the pivot tables must both have the same set of possible values, and if this isn't the case you should add a dummy entry to use as a fail safe. Here we see the event trigger checking the pivot table is the right one... without this we'd potentially be firing the event macro for every change we make to the target table!



Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

If Target = ActiveSheet.PivotTables("PivotTable10") Then

AlignPivots

End If

End Sub



Sub AlignPivots()

' This aligns pSummary with pDetail. Piv1 is pSummary: this is our SOURCE. Piv2 is pDetail: this is our TARGET.

Dim Pitem As Integer

Dim Plabel As String

Dim Piv1 As PivotTable, Piv2 As PivotTable

Set Piv1 = Sheets("Items").PivotTables("pSummary")

Set Piv2 = Sheets("Details").PivotTables("pDetail")

' Align Product type. Start by setting any of them to visible, so we never try and make our target pivot table

' show nothing. Process the other entries, setting visible true or false as required, and finally process the

' one I set visible to start with. The choice is random, but let's just do the first entry.

With Piv2.PivotFields("Product type")

.PivotItems(1).Visible = True

For Pitem = 2 To .PivotItems.Count

Plabel = .PivotItems(Pitem).Value

.PivotItems(Pitem).Visible = Piv1.PivotFields("Product type").PivotItems(Plabel).Visible

Next Pitem

Plabel = .PivotItems(1).Value

.PivotItems(1).Visible = Piv1.PivotFields("Product type").PivotItems(Plabel).Visible

End With

' Align Site. Again I start by setting the first one to visible and then mopping up at the end.

With Piv2.PivotFields("Site")

.PivotItems(1).Visible = True

For Pitem = 2 To .PivotItems.Count

Plabel = .PivotItems(Pitem).Value

.PivotItems(Pitem).Visible = Piv1.PivotFields("Site").PivotItems(Plabel).Visible

Next Pitem

Plabel = .PivotItems(1).Value

.PivotItems(1).Visible = Piv1.PivotFields("Site").PivotItems(Plabel).Visible

End With

End Sub

Sorry to be harsh, but I'm afraid I don't find this tip very useful.

For one thing, the uninitiated possibly won't understand terms such as activate/deactivate, Change or Calculate, and also I'm a bit fuzzy on what FollowHyperlink entails (such as, can you use it in deciding whether or not to follow the hyperlink, or is the hyperlinking a done deal?).

For another, the tip says nothing about scope management. By this, I mean checking that the event trigger is relevant, or in other words, say you want to pick up when cell A1 changes, how do you ensure you only run the macro when this cell changes rather than any other cell? Because without scope, and especially if the macro itself affects anything in the workbook, you could find yourself in a seemingly perpetual loop of nested events, each macro firing the trigger for another. The typical way to check the scope is correct is by using the "if Not Interesct (Target, {A1}) Is Nothing" syntax that readers can Google.

Finally, you don't say why you'd want to do this or give any examples. I will provide a couple of examples in a separate post.