Help setting scale limits on chart

[Jerome Ranch]

I have a chart based on data in an xls sheet.
Both the x and y data data change when calculations are made.
So I don't want the x axis variable to be fixed, per se, but I'd like
the scale limit dialog to allow me to put in the cell on the sheet to
set the lower and upper limit.
So when I place the cell address into the dialog for min and max I get
an error
I tried both the address (i.e. N2, or like this =N2) both were errored
out.

Is there no way to do this??

I guess I need to build a macro instead that always selects the same
cells and have the macro built the chart that way? I didn't want to
build the chart fresh each time.

Thanks
Jerry

 

[sswilcox]

I had the exact same problem and found this solution. I've been using
it now for 4-5 months and am quite happy with it.

http://www.tushar-mehta.com/excel/software/autochart/

This is a free Add-In called "AutoChart" that will accomplish what you
need. The catch is that all users will also need to install the add-in
on their own machines.

S

 

[Scott Collier]

I am sure there is a better way but this may put you on the right track.

Put the following code in the "ThisWorkbook" section/module of the VB
editor.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Sh.ChartObjects("Chart 1").Activate
With ActiveChart.Axes(xlValue)
.MinimumScale = Range("A2").Value
.MaximumScale = Range("A3").Value
End With
Target.Activate

End Sub

Change chart name and ranges to suit.