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.