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.
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.
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.
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.