Hi

On a sheet I have a simple pivot table with Year (Row), £ Sales (Values), and Product Type (Report Filter).

I then have a Line chart running off this.


I'm trying to write a code so when the Product Type filter changes, the chart axis scales update depending on a Cell (C1) which simple diplays which Product is selected.
Basically, I want it to also be 'Auto' unless "All Hard Goods" are selected, whereby I want the scale to be Min: 2000000 and Max: 3000000.

I'm fairly new to vba, and so far I've tried these, with no luck:


This doesn't do what I need:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim prod As Range

Set prod = Sheets("Sheet 1").Range("C1")

 If prod.Value = "All Hard Goods" Then

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MaximumScale = 3000000
    ActiveChart.Axes(xlValue).MinimumScale = 2000000

  Else
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MinimumScaleIsAuto = True
    ActiveChart.Axes(xlValue).MaximumScaleIsAuto = True

  End If

End Sub

And this comes up with Compile Error: Object Required:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Dim prod As String

Set prod = Sheets("Sheet 1").Range("C1")

 If prod.Value = "All Hard Goods" Then

     ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MaximumScale = 3000000
    ActiveChart.Axes(xlValue).MinimumScale = 2000000

  Else
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.Axes(xlValue).Select
    ActiveChart.Axes(xlValue).MinimumScaleIsAuto = True
    ActiveChart.Axes(xlValue).MaximumScaleIsAuto = True
 End If

End Sub
Any help would be most appreciated. Thanks