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