setting min and max scale in graph

namsilat

New Member
Joined
Mar 8, 2011
Messages
13
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.
 
Last edited:

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"

namsilat

New Member
Joined
Mar 8, 2011
Messages
13
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

MrExcel MVP
Joined
May 14, 2003
Messages
4,968
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Mar 8, 2011
Messages
13

ADVERTISEMENT

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

MrExcel MVP
Joined
May 14, 2003
Messages
4,968
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Replace this part:

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

with this:

Charts("Score").

(including the dot)
 

namsilat

New Member
Joined
Mar 8, 2011
Messages
13

ADVERTISEMENT

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

MrExcel MVP
Joined
May 14, 2003
Messages
4,968
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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

New Member
Joined
Mar 8, 2011
Messages
13
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

MrExcel MVP
Joined
May 14, 2003
Messages
4,968
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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.
 

Forum statistics

Threads
1,141,019
Messages
5,703,774
Members
421,315
Latest member
awaisnazir139

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top