Adjusting chart Y-axis value using VBA

**goels** · 11-07-2011, 06:49 AM

Hi All,

I have a chart whose minimum value I would like to adjust based on the minimum value in a named range. So for eg if my named range is called Average_weight and if the minimum value of this named range is 40 then I would like the chart's Y-axis to have a minimum value of 40.

How could I achieve this? Also I would like it to be done such that the minimum value is a rounded down figure i.e. if the minimum value of the named range comes to be 38.76 the minimum value of the Y-axis should be 30. If the minimum value comes to be 22.3 the minimum value of the Y-axis should be 20. This would let me maintain intervals of 10 on my Y-axis.

Many Thanks

**Andy Pope** · 11-07-2011, 06:58 AM

This example uses a UDF to set scale values.
http://peltiertech.com/Excel/Charts/...nkToSheet.html

You can use the FLOOR formula to calculate the new min value to use.

**goels** · 11-07-2011, 07:35 AM

Thanks Andy.

I have managed to get it working but one improvement remains. Currently the value is coming from the same worksheet as the one on which the chart is. But I want to place the value on another worksheet and then access it in the code from there. How do I change the 4th line to do this. So for eg if the worksheet I want to place the value in is called 'Weight' how do I call it in the 5th line?

Thanks

AlignY ThisWorkbook.Worksheets("Report").ChartObjects("Chart 75").Chart

Sub AlignY(Cht As Chart)

With Cht
With .Axes(xlValue, xlPrimary)
.MinimumScale = ActiveSheet.Range("Y548").Value
.MaximumScale = 1
End With

End With
End Sub

**Andy Pope** · 11-07-2011, 07:37 AM

Just reference the worksheet rather than the activesheet

.MinimumScale = Worksheets("Weight").Range("Y548").Value

**goels** · 11-07-2011, 07:41 AM

Star! - thank you very much.

**goels** · 11-07-2011, 08:04 AM

Thanks Andy.

I have managed to get it working but one improvement remains. Currently the value is coming from the same worksheet as the one on which the chart is. But I want to place the value on another worksheet and then access it in the code from there. How do I change the 4th line to do this. So for eg if the worksheet I want to place the value in is called 'Weight' how do I call it in the 4th line?

Thanks

AlignY ThisWorkbook.Worksheets("Report").ChartObjects("Chart 75").Chart

Sub AlignY(Cht As Chart)

With Cht
With .Axes(xlValue, xlPrimary)
.MinimumScale = ActiveSheet.Range("Y548").Value
.MaximumScale = 1
End With

End With
End Sub