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