Automatic maximum axis scale but not greater than given value

[Andoni]

Hi all,

I am trying to create a chart where the maximum axis scale is automaticaly set (MaximumScaleIsAuto = True) but the Maximum of the axis scale can't be greater than a given valule (e.g. 100%)

Any ideas?

Thanks for your help!

 

[Chihiro]

Something like this?
http://chandoo.org/forum/threads/excel-chart-axis-scale-from-cell.25284/#post-151460

 

[Andoni]

Well this is a bit different.

I create many charts with figures between 0%-100% and I want the maximum axis scale to be automatically adjusted (MaximumScaleIsAuto = True).

Most of the charts have figures between 0-95% and the maximum scale is automatically set between 0% and 100% and this is OK.

The issue is that when I have values between 96% and 100% the axis automatically scales to 120%.

I am looking for something that automatically adjusts the maximum axis but never gives a scale greater than 100%.

Thanks!

 

[Chihiro]

In that instance, you can either

1) Set Maximum scale to 100% since it sounds like max will always be 100%.

2) Use the code provided in link and have calculation done outside the table to set maximum for each chart.

3) Set maximum to auto, but check each chart via VBA for .MaximumScale and if it is larger than x. Change value to y. Something like below to get the .MaximumScale and then do your operation

Dim mAxis As Long
With ActiveChart.Axes(xlValue, xlPrimary)
    mAxis = .MaximumScale
End With