Link Chart Axis to Cell Value

[pagrender]

Hello all,

I ran into some issues regarding how Excel automatically scaled some of my charts - mainly I didn't like how it determined what should be the maximum or the minimum values of the Y axis of my Line chart.

Manually changing the scaling isn't an option I would like to pursue as when my project is done I will have about 800 charts in numerous files.

I found some VB code out on a website where I can enter a value in a cell and have that value go to the chart (http://en.allexperts.com/q/Excel-1059/2008/11/Link-Chart-Axis-Formulas.htm), modified it just a bit, but I cannot make it work.

Here is the code I have:
Sub minMax()
Dim wks As Worksheet
Dim cht As ChartObject
Set wks = ThisWorkbook.Sheets("Charts")
Set cht = wks.ChartObjects("Chart 3")
cht.Chart.Axes(xlValue).MinimumScale = wks.Range("a1")
cht.Chart.Axes(xlValue).MaximumScale = wks.Range("b1")
End Sub

In A1 on the "Charts" sheet, I entered my minimum and in B1 I entered the maximum.

When I run the macro, the Y axis changes to a bunch of zeros and negative ones, along with moving the Y axis to the top of the chart.

Can someone help me fix this code?

Thanks,
Pete

 

[pagrender]

Bump

 

[pagrender]

Does anyone have any suggestions how fix this code? Or do they have their own code suggestion?

 

[pagrender]

I found some code on a different website that I was able to make work:

http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html