Excel 2003 VBA - changing axis scale upon worksheet change

culpees
culpees used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
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
    ActiveChart.Axes(xlValue).Select
    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 Archibald
Most Valuable Expert 2011
Top Expert 2011

Commented:
FYI, it should work with named ranges too if you use
Range("MaxScale_LP")
syntax.
culpees

Author

Commented:
I resolved it before anyone else bothered to answer.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial