Adjusting scale on Slicer

[MPaulus]

I have created my pivot table and pivot chart. I have added the following code to have the scale on the secondary axis match the primary axis:

Dim x As Long


    With ActiveSheet.ChartObjects("Pivot").Chart

        ActiveSheet.ChartObjects("Pivot").Activate
        ActiveChart.Axes(xlValue).MaximumScaleIsAuto = True
        ActiveChart.Axes(xlValue).MinimumScale = 0
        x = ActiveChart.Axes(xlValue).MaximumScale
        ActiveChart.Axes(xlValue, xlSecondary).MaximumScale = x

The problem is that now when I use my slicer the secondary axis scale remains the same as the summary and does not adjust to match the individual selections.

 

[MPaulus]

Wondering if anyone could help me with this?

 

[Domenic]

Here, I think you can use the PivotTableUpdate event handler. So, every time you select a slicer item, the pivot table will be updated, the event handler will be executed, and the secondary axis scale will be set according to the primary axis scale. Right-click the sheet tab, select View Code, and copy/paste the following code into the code module for the sheet. . .

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    With Target.PivotChart.Chart
        .Axes(xlValue, xlSecondary).MaximumScale = .Axes(xlValue).MaximumScale
    End With
End Sub

Hope this helps!

 

[MPaulus]

I get a run-time error '1004': Application-defined or object-defined error. It points to

With Target.PivotChart.Chart

 

[Domenic]

Interesting. Prior to posting my code, I tested it and it seemed to work fine. Now that I have tested it again, I get the same error as you. So assuming that the pivot chart is located on the same worksheet as the pivot table, right-click the sheet tab, select View Code, and copy/paste the following code into the code module for the sheet...

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
    With Me.ChartObjects(1).Chart
        .Axes(xlValue, xlSecondary).MaximumScale = .Axes(xlValue).MaximumScale
    End With
End Sub

Does this help?

 

[MPaulus]

@Domenic, you are a life saver! Thank you for all of your help!