Axis scaling - Jon's code

  • Thread starter Thread starter FT
  • Start date Start date
F

FT

I have been trying to use the code that Jon posted to this
newsgroup:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Select Case Target.Address
Case "$E$2"
ActiveWorkbook.Charts("Chart1").Chart.Axes
(xlCategory) _
.MaximumScale = Target.Value
Case "$E$3"
ActiveWorkbook.Charts("Chart1").Chart.Axes
(xlCategory) _
.MinimumScale = Target.Value
Case "$E$4"
ActiveWorkbook.Charts("Chart1").Chart.Axes
(xlCategory) _
.MajorUnit = Target.Value
Case "$F$2"
ActiveWorkbook.Charts("Chart1").Chart.Axes
(xlValue) _
.MaximumScale = Target.Value
Case "$F$3"
ActiveWorkbook.Charts("Chart1").Chart.Axes
(xlValue). _
MinimumScale = Target.Value
Case "$F$4"
ActiveWorkbook.Charts("Chart1").Chart.Axes
(xlValue) _
.MajorUnit = Target.Value
Case Else
End Select
End Sub

.. . . but I keep getting errors: either "Object doesn't
support this property or method" or, in one weird
case, "Subscript out of range". I assume this is a
question for Jon: what am I doing wrong!?
 
I am going to take the liberty of anwerign my own post, in
case anyone else is interested. Jon originally posted
this code on the newsgroup so that it would change axes
(and scale) for a chart object embedded on a worksheet. I
have been trying to use it for a chart on a separate
sheet, but I didn't modify the reference correctly. For
those that also are trying to sue this code, do the
following:

For an chart on an worksheet page use:

ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory)
.MaximumScale = Target.Value

For a chart on a separate sheet use:

ActiveWorkbook.Charts("ChartName").Axes(xlCategory) _
.MaximumScale = Target.Value

I hope this helps. Thanks to Jon for showing the way.
 
Back
Top