Excel 2003 VBA - changing axis scale upon worksheet change

Worksheet with combo boxes. User selects combos linked to a cell and the chart changes according to selections. Need the axis to also adjust to my named ranges.  I would like this to be upon a worksheet_change vs manually calling the macro/code if possible.

I've tried many different ways of doing this and have nothing workable yet. I need this asap and have spent many many hours researching this from examples.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ch As ChartObject
    Set ch = Worksheets(Summary).ChartObjects("Chart 16").Select
    With ch.Axes(xlValue, xlSecondary)
        .MinimumScale = ActiveSheet.Name("MinScale_LP")
        .MaximumScale = ActiveSheet.Name("MaxScale_LP")
    End With
    With ch.Axes(xlValue, xlSecondary)
        .MinimumScale = Summary.Name("MinScale_GFWC")
        .MaximumScale = Summary.Name("MaxScale_GFWC")
    End With
End Sub

This did not work either:
Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.ChartObjects("Chart 16").Chart.Axes(xlValue).MaximumScale = MaxScale_LP
    ActiveSheet.ChartObjects("Chart 16").Chart.Axes(xlValue).MinimumScale = MinScale_LP
    ActiveSheet.ChartObjects("Chart 16").Chart.Axes(xlValue, xlSecondary).MaximumScale = MaxScale_GFWC
    ActiveSheet.ChartObjects("Chart 16").Chart.Axes(xlValue, xlSecondary).MinimumScale = MinScale_GFWC

End Sub
Who is Participating?
culpeesConnect With a Mentor Author Commented:
Nevermind. I figured it out.
I recorded a macro to see what Excel wrote for the code and came up with this:
Private Sub Worksheet_Change(ByVal Target As Range)

    ActiveSheet.ChartObjects("Chart 16").Activate
    With ActiveChart.Axes(xlValue)
        .MinimumScale = Range("$Q$32")
        .MaximumScale = Range("$r$32")
        .MajorUnitIsAuto = True
        .MajorUnit = Range("$S$32")
        .MinorUnitIsAuto = Range("$T$32")
        .MinorUnit = Range("$u$32")
    End With
    ActiveChart.Axes(xlValue, xlSecondary).Select
    With ActiveChart.Axes(xlValue, xlSecondary)
        .MinimumScale = Range("$Q$33")
        .MaximumScale = Range("$r$33")
        .MajorUnitIsAuto = True
        .MajorUnit = Range("$S$33")
        .MinorUnitIsAuto = Range("$T$33")
        .MinorUnit = Range("$u$33")
    End With
End Sub

It wasn't working with defined names, so I had to use ranges.
Rory ArchibaldCommented:
FYI, it should work with named ranges too if you use
culpeesAuthor Commented:
I resolved it before anyone else bothered to answer.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.