Automatic Scaling of axis in chart

[ruud29]

Hello,

i'm a creating a sheet for displaying different ranges of values. With a button i display these values in a chart.
My problem is that the scaling of Y-axis in the chart is not done automaticaly sometimes.
The values on the Y-axis vary from eg 0,01 in one case to 5000 in an other case.
In some of the cases the scaling is adjusted to around the minimal and maximal value of the data and in other cases it's displayed from 0 to 5000 with ticks of 100.
I have this piece of code in a macro:

With ActiveChart.Axes(xlValue)
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
End With

But this doesn't always seem to work.
CAn anybody help me with this problem?
Please be very specific in your answer, because I'm quite new to VB.

Thanks for your help so far.

 

[ETID]

You'll have to write some code to check if the previous maximum is less than the new minimum ...if so the order in which they are set in the code needs to be reversed.

 

[RobBroekhuis]

What you're experiencing may not have anything to do with your VBA coding, and more with Excel's criteria for assigning automatic axis limits. Check on your actual graph after you made the assignments above to see if the scale is set to automatic. If so, it's just the way Excel decides to set the limits. If you don't like them, you'll have to define your own criteria, and assign them explicitly. Let us know how you fare.
Rob

 

[ruud29]

I think I've figured out what the problem was.
I was also displaying some other values in this same chart, but those values were sometimes alphanumeric. That causes them to be displayed as zero.
I now made a form where the users can enter a minumum and maximum scale for the Y-axis.
That works just fine.
Thanks to all for posting their ideas.