Value Y Axis Scale on a line chart

Posted by Don on November 23, 2000 3:00 PM

I have created a line chart (Chart1 As Object in sheet2) of a workbook.All the values that are being plotted on the chart are coming from Sheet1 in the same workbook. I need to change the Value Y Axis Scale (Maximum and Minimum)from a value in a cell on sheet1 of the workbook. For instance, if the value in cell A1 on sheet1 was 40 and the value in cell A2 on sheet1 was 60 then the minimum Value Y Axis scale would be 40 and the maximum Value Y Axis scale would be 60. I need the axis scale to change every time the values in these two cell change.
Thanks Don

Posted by Kar on November 25, 2000 3:46 AM

Posted by Kar on November 25, 2000 3:47 AM

Posted by Kar on November 25, 2000 4:06 AM

Try the following Code
With Worksheet(2).ChartObjects(1).Chart
With .Axes(xlValue)
.MinimumScale = Worksheets("Sheet1").Range("A1").Value
.MaximumScale = Worksheets("Sheet2").Range("A").Value

Posted by Ivan Moala on November 25, 2000 4:52 AM

Don you didn't specify how the values change
If the values in A1 & A2 change via user input
then in sheet1 put this code in;

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address = "$A$1" Or Target.Address = "$A$2" Then
Call Chg_MinMax(Range("A1"), Range("A2"))
End If
End Sub

In a Module place the folowing code;

Sub Chg_MinMax(Min As Integer, Max As Integer)
Application.ScreenUpdating = False
Sheets("Sheet2").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.Axes(xlValue).Select
With ActiveChart.Axes(xlValue)
.MinimumScale = Min
.MaximumScale = Max
End With
Sheets("Sheet1").Activate
Application.ScreenUpdating = True

End Sub

so that if you change the values @ A1&A2 then the
chart is automatically updated.
If these values are changed vai formulas then
you will have to use the worksheet Caculate event
to update this automatically.

Ivan