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

cmr72

New Member
Joined
Feb 19, 2013
Messages
43
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:

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:
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
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

ParamRay

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
.
.

Try this:

Code:
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

New Member
Joined
Feb 19, 2013
Messages
43
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

New Member
Joined
Feb 19, 2013
Messages
43
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

Well-known Member
Joined
Aug 6, 2014
Messages
1,195
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.)
 

Watch MrExcel Video

Forum statistics

Threads
1,109,027
Messages
5,526,329
Members
409,696
Latest member
EERS

This Week's Hot Topics

Top