Keeping Series Formating in Pivot Charts

[calandrelli]

In Excel if you create a pivot chart and add custom formatting to one of the series, Excel resets it back to default each time you change a data selection. This is troublesome, especially with data that you wish to display using two axes. Microsoft states that this is a known issue and suggests recording a macro of your formatting. Although this was a good starting place they don't go any further.

Most users don't want to run a macro after they update the chart. They want the update to be seamless. I decided that attaching the recorded format code to a chart event would be the best idea, but I was wrong because the only event that would capture a data selection would be "Calculate". Unfortunately, code to change the chart in the change event causes an infinite loop.

The Solution:
The Pivot Table updates every time the Pivot Chart changes so the Worksheet_PivotTableUpdate event can be used to fire off the Pivot Chart formatting.

The following code changes the chart type to a dual axis chart and adjusts the primary axis scale to 0-100 and the secondary axis scale to 0-1.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Charts("Chart3").Activate
With ActiveChart
    .ApplyCustomType ChartType:=xlBuiltIn, TypeName:="Line - Column on 2 Axes"
    .Axes(xlValue).MinimumScale = 0
    .Axes(xlValue).MaximumScale = 100
    .Axes(xlValue, xlSecondary).MinimumScale = 0
    .Axes(xlValue, xlSecondary).MaximumScale = 1
End With
End Sub

I hope this example helps.

 

[SkipVought]

calandrelli,

This is the kind of tip that ought to be recorded in an FAQ.

However, it is almost too specialized. You have your formatting; someone else has theirs. Maybe a comment in the code to "put your format code here". But it would take someone knowing to macro record the formatting change, copy the code and place the "right stuff" in this area.

And what if this is not the FIRST chart in their workbook? I attempted to generalize that issue as follows...

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    Dim ch As Chart, sRefersTo As String
    For Each ch In Charts
        sRefersTo = Split(Split(ch.SeriesCollection(1).Formula, "(")(1), ",")(0)
        If Target.Parent.Name = Split(sRefersTo, "!")(0) Then
            If Not Intersect(Target.TableRange1, Range(Split(sRefersTo, "!")(1))) Is Nothing Then
                With ch
        'vvv put your macro recorded formatting code here.
                    .ApplyCustomType _
                        ChartType:=xlBuiltIn, _
                        TypeName:="Line - Column on 2 Axes"
                    .Axes(xlValue).MinimumScale = 0
                    .Axes(xlValue).MaximumScale = 100
                    .Axes(xlValue, xlSecondary).MinimumScale = 0
                    .Axes(xlValue, xlSecondary).MaximumScale = 1
        '^^^ put your macro recorded formatting code here
                End With
            End If
        End If
    Next
End Sub

Skip,
[sub]
Have you heard that the roundest knight at King Arthur's round table was...
Sir Cumference![/sub]