setting min and max scale in graph

[namsilat]

I have my source data in sheet 1 of the spreadsheet, while the graph is in sheet 2. I am trying to set the min and max of graph based on values in certain cells in sheet 1. Could someone suggest such a macro for Excel 2003? Thanks.

 

[Jon Peltier]

See if my old tutorial is helpful:
http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html

 

[namsilat]

Hi there,

I tried the codes under "Worksheet_Change Event" and received following error:

Run-time error 1004:
Unable to get the ChartObjects property of the Worksheet class

I don't know if it has anything to do with the name and location of the chart. On the top left panel of Visual Basic Editor, the chart is listed as "Chart2(Score)", so I changed "Chart 1" to "Score" in the codes, but it still did not work.

 

[Jon Peltier]

Is the chart embedded in a worksheet? If so, you refer to it as

Worksheets("name of worksheet").ChartObjects("name of chart").Chart

You could use the number of the chart, for instance, if there's only one chart embedded in that worksheet, use 1.

Or is the chart on its own chart sheet (no rows and columns, just the chart)? Then you can refer to

Charts("tab name of chart")

On second reading, I think you need to refer to Charts("Score")

 

[namsilat]

Hi there,

I am not entirely clear on which part of the code I should replace. Did you mean replacing ChartObjects("Chart 1") with Charts("Score")?


</pre>

 

[Jon Peltier]

Replace this part:

ActiveSheet.ChartObjects("Chart 1").Chart.

with this:

Charts("Score").

(including the dot)

 

[namsilat]

Charts("Score").Axes(xlValue) _
.MinimumScale = Target.Value

I now get a new error, Run-time error '9', Subscript out of range. I am not sure what it's referring to. The minimum scale is out of range?

 

[Jon Peltier]

Is the tab name (not the code name that you see in the VBE) actually "Score", not "Scores" or "Score " or some other subtle variation? Excel can't do subtle.

 

[namsilat]

I went back to the spreadsheet and for whatever reason it's now working, so thank you very much for the help. The only little issue is that when the cells containing the max and min values are altered by calculations done by the spreadsheet, the scales of the graph are not changed. If I manually go to the cell, hit F2 and just hit Enter, then it's seen as a change.

 

[Jon Peltier]

What you can do is store the values in a hidden location. Then trap the Calculate event, compare values in the magic axis cells to the values you saved earlier. Only if they are different, change the chart, and update the hidden values.