auto adjust Y axis to min/max of a specific range

[cmr72]

Hello, quick question from a novice.

I have a sheet named Dashboard, where $O$4 = MIN and $P$4 = MAX of a specific range; chart is "Chart 5"

I want to have the Y axis adjust to these values instead of 0 and some max range that Excel sets. I don't want to set the Y axis manually each time for different values.

I found a snippet online and tried to modify it but am getting errors.

Original code:

[COLOR=#000000][FONT=Arial]Sub adjustscales()
[/FONT][/COLOR][COLOR=#000000][FONT=Arial]Sheet1.ChartObjects("Chart 1").Chart.Axes(xlValue).MinimumScale = Sheet1.Range("A1")[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]Sheet1.ChartObjects("Chart 1").Chart.Axes(xlValue).MaximumScale = Sheet1.Range("A2")[/FONT][/COLOR]
[COLOR=#000000][FONT=Arial]End Sub[/FONT][/COLOR]

My code:

[COLOR=#000000][FONT=Arial][COLOR=#222222][FONT=Verdana]Sub adjustscales()[/FONT][/COLOR][/FONT][/COLOR]
Dashboard.ChartObjects("Chart 5").Chart.Axes(ylValue).MinimumScale = Dashboard.Range("O4")
Dashboard.ChartObjects("Chart 5").Chart.Axes(ylValue).MaximumScale = Dashboard.Range("P4")
End Sub

I'm getting run-time error '424' Object required.

Any help is appreciated.

Thanks,
Craig

 

[ParamRay]

.
.

Try this:

Sub AxisScale()

    Dim WS As Worksheet
    Set WS = Worksheets("Dashboard")

    With WS.ChartObjects("Chart 5").Chart.Axes(xlValue)
        .MinimumScale = WS.Range("O4").Value
        .MaximumScale = WS.Range("P4").Value
    End With

End Sub

 

[cmr72]

That worked really well, thank you.

How does one differentiate between the X axis and Y axis using only "xlValue"?

As stated before, I'm a novice and I don't see where the axes are defined.

 

[cmr72]

Also, I was wondering how to have this invoke automatically.

I think I need to use: Worksheet_SelectionChange but I'm not sure how.

Thanks.

 

[ParamRay]

That worked really well, thank you.

How does one differentiate between the X axis and Y axis using only "xlValue"?

As stated before, I'm a novice and I don't see where the axes are defined.

Usually, the horizontal axis is xlCategory and the vertical axis is xlValue. (The "XL" just means it's an Excel constant; it does not refer to the orientation of the axis.)