I found the following code to change the scale of the primary x and y axes of a chart by changing cells in a worksheet. The existing chart has a secondary axis that I would like to be able to also change by changing a cell value as well. When I copied the code and modified it so that the AxisGroup specified the seconadry axis, the code doesn't work. The code is in the worksheet module page and not a stand alone module. I can not figure out what is going on. Any help will be very appreciated. I will be glad to provide more detail is needed. Here is the code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$L$60"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory) _
.MaximumScale = Target.Value
Case "$L$61"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory) _
.MinimumScale = Target.Value
Case "$L$62"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory) _
.MajorUnit = Target.Value
Case "$L$63"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlCategory) _
.MinorUnit = Target.Value
Case "$M$60"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) _
.MaximumScale = Target.Value
Case "$M$61"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) _
.MinimumScale = Target.Value
Case "$M$62"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) _
.MajorUnit = Target.Value
Case "$M$63"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(xlValue) _
.MinorUnit = Target.Value
'the above code is the original part that works perfectly
'everything below was copied from above and modified to specify the secondary axis
Case "$N$60"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(x1Category, x1Secondary) _
.MaximumScale = Target.Value
Case "$N$61"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(x1Category, xlSecondary) _
.MinimumScale = Target.Value
Case "$N$62"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(x1Category, xlSecondary) _
.MajorUnit = Target.Value
Case "$N$63"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(x1Category, xlSecondary) _
.MinorUnit = Target.Value
Case "$O$60"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(x1Value, xlSecondary) _
.MaximumScale = Target.Value
Case "$O$61"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(x1Value, xlSecondary) _
.MinimumScale = Target.Value
Case "$O$62"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(x1Value, xlSecondary) _
.MajorUnit = Target.Value
Case "$O$63"
ActiveSheet.ChartObjects("Chart 1").Chart.Axes(x1Value, xlSecondary) _
.MinorUnit = Target.Value
Case Else
End Select
End Sub
Bookmarks