Auto-scaling of graph axes

[Dave]

Hi

I have a series of graphs which I use each month to graph
data from several different locations.

The graphs are all simple line graphs, but the range of
numbers can vary a lot.

Because of this, I set the scale on the y-axis to auto-
maximum and auto-minimum, so the chart will adjust
automatically and present the data better.

However, if I set the auto-minimum, it always
returns "0". What I want is for the graph to
automatically set me a minimum on the y-axis, but for this
minimum to not necessarily be "0" (ie it may be 30 for
one location, but 50 for anther, and so on).

Dave

[Jon Peltier]

Hey Dave -

You will have to use a macro to link your axis scales to worksheet data.
Perhaps the easiest way to do this is to download Tushar Mehta's
Autochart Manager, a free addin at
http://www.tushar-mehta.com/excel/software/autochart/index.html

You could write a macro to apply values in the worksheet to the chart
axis. Suppose you put your Y-min, Y-max, Y-major, and Y-minor values
into A1:A4, a macro would look like this:

Sub ScaleYAxis()
' Select the chart, then run the macro
Dim mySheet As Worksheet
Dim myChart As Chart
Dim myRange As Range
Set mySheet = ActiveSheet
Set myChart = ActiveChart
Set myRange = Application.InputBox _
(prompt:="Please Select a Range" & vbCrLf & vbCrLf & _
"Top to Bottom: Ymin - Ymax - Major - Minor", _
Title:="Enter Range", Type:=8)
With myChart.Axes(xlValue)
.MinimumScale = myRange.Range("A1")
.MaximumScale = myRange.Range("A2")
.MajorUnit = myRange.Range("A3")
.MinorUnit = myRange.Range("A4")
End With
End Sub

This macro belongs in a regular code module. Press Alt-F11 to open the
VB Editor, choose Module from the Insert menu, and paste in the code
above. Select the chart, then run the macro. You can make a command
bar button and assign it to this macro. Or you could write a
worksheet-change even procedure that runs whenever one of these values
changes. This looks like this:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1:A4")) Is Nothing Then
ScaleYAxis
End If
End Sub

This one goes in the code module for the worksheet. Right click on the
sheet tab, select View Code, and paste this macro in the module that
appears.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
http://www.geocities.com/jonpeltier/Excel/index.html
_______