VBA code error

[dan03uk]

Need some help with this VBA code, I can't see why it doesn't work.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Select Case Target.Address
    Case "$E$2"
        ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlCategory) _
            .MaximumScale = Target.Value
    Case "$E$3"
        ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlCategory) _
            .MinimumScale = Target.Value
    Case "$E$4"
        ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlCategory) _
            .MajorUnit = Target.Value
    Case "$F$2"
        ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue) _
            .MaximumScale = Target.Value
    Case "$F$3"
        ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue) _
            .MinimumScale = Target.Value
    Case "$F$4"
        ActiveSheet.ChartObjects("Chart 2").Chart.Axes(xlValue) _
            .MajorUnit = Target.Value
    Case Else
End Select
End Sub

 

[Andrew Poulsom]

In what way isn't it working?

The Workbook_SheetChange event applies to every sheet in the workbook. ActiveSheet can be replaced by Sh, which is passed as an argument to the procedure.

Is it the intention to monitor changes on every worksheet or just one? If the latter you should use the Worksheet_Change event procedure.

 

[dan03uk]

Hi thanks fir the help. It only neeeds to be changed on the actuve sheet so I will give what you said ago.

 

[dan03uk]

Hi it kinda worked it work for the x1value, but didn't work for x1category values.

 

[Andrew Poulsom]

What sort of chart do you have? It needs to be XY(Scatter) to change the scaling on the category axis.

 

[dan03uk]

Hi it kinda worked it work for the x1value, but didn't work for x1category values.

 

[dan03uk]

Yeh im using an xy scatter graph

 

[Andrew Poulsom]

Hi it kinda worked it work for the x1value, but didn't work for x1category values.

You already said that, but you haven't answered my question about the chart's type.

 

[dan03uk]

Hi thanks so much for your help i got it working, I was using the wrong address for the chart.

 

[dan03uk]

One more wuick question is there away of stoping it from linking to ever sheet as it currently changes the axis on all sheets.