Edit 22 Mar 2014: Also see https://newtonexcelbach.wordpress.com/2012/11/25/automating-chart-scale-limits-update/

for improved version with download link, and example of plotting a chart from a formula entered as text.

One of the more annoying things about Excel charts is that if you want to over-ride the automatic scale limits there is no built-in way to link the limits to a spreadsheet cell, so you have to go into the dialog box and change the numbers manually every time you want to change the scale.

Tushar Mehta recently posted a solution at Daily Dose of Excel, with an add-in providing this functionality, as well as other format chart adjustments.

In the following discussion John Walkenbach linked to an earlier solution that those who like to keep things simple may prefer. This is a user defined function (UDF), that returns no data, but modifies the y-axis limits of any named chart. It should be noted that this solution only works in Excel 2007 and later, is undocumented, and is not supposed to work at all, so use with caution. I have taken the liberty of modifying John’s code so that the limits of both the X and Y axes can be linked to cell values, the axes limits can be re-set to automatic, and the status of each axis is returned by the function. The revised code and a screen shot are given below.

Function ChangeChartAxisScale(CName As String, Optional Xlower As Double = 0, Optional Xupper As Double = 0, _
Optional Ylower As Double = 0, Optional YUpper As Double = 0) As Variant
Dim Rtn As String
' Excel 2007 only
With ActiveSheet.Shapes(CName).Chart.Axes(1)
If Xlower = 0 Then
.MinimumScaleIsAuto = True
Rtn = "Xmin = auto"
Else:
.MinimumScale = Xlower
Rtn = "Xmin = " & Xlower
End If
If Xupper = 0 Or (Xupper < Xlower) Then
.MaximumScaleIsAuto = True
Rtn = Rtn & "; Xmax = auto"
Else
.MaximumScale = Xupper
Rtn = Rtn & "; Xmax = " & Xupper
End If
End With
With ActiveSheet.Shapes(CName).Chart.Axes(2)
If Ylower = 0 Then
.MinimumScaleIsAuto = True
Rtn = Rtn & "; Ymin = auto"
Else
.MinimumScale = Ylower
Rtn = Rtn & "; Ymin = " & Ylower
End If
If YUpper = 0 Or (Xupper < Xlower) Then
.MaximumScaleIsAuto = True
Rtn = Rtn & "; Ymax = auto"
Else
.MaximumScale = YUpper
Rtn = Rtn & "; Ymax = " & YUpper
End If
End With
ChangeChartAxisScale = Rtn
End Function

ChangeChartAxisScale Function, Click for full size view

### Like this:

Like Loading...

*Related*

great tip… is there a way to easily adjust this to work when there are 2 y-axes on the chart?

thanks.

LikeLike

Mike – I don’t know, but I’ll have a look when I have time.

LikeLike

I changed the routine to accept an additional optional variable

changeaxis(…, optional yaxis as integer=1)

then change the yaxis bit to:

‘ add this —————————–

If yaxis = 2 Then

Set myaxis = ActiveSheet.Shapes(CName).Chart.Axes(2, xlSecondary)

Else

Set myaxis = ActiveSheet.Shapes(CName).Chart.Axes(2)

End If

‘ ——————————————–

With myaxis ‘ was just …. ActiveSheet.Shapes(CName).Chart.Axes(2)

etc…

LikeLike

Thanks brian, I’ll post an update.

LikeLike

Pingback: Automating chart scale limits – update | Newton Excel Bach, not (just) an Excel Blog

Pingback: Charts and Charting | Newton Excel Bach, not (just) an Excel Blog

how to integrate this in Visual Studio and make it work on SharePoint

LikeLike

I can’t help with that one I’m afraid, I don’t even know if it is possible. It is not clear to me how SharePoint relates to OneDrive, and what facilities each offers.

LikeLike