CHARTING CODE ERROR

[Jase]

I have this code that I am trying to change the scaling from cells on the
worksheet. However it is giving me an error saying "unable to get the
ChartObjects property of the worksheet class" Any ideas?


Private Sub Worksheet_Change(ByVal Target As Range)

Dim iChart
Dim cht

If Not Intersect(Target, Range("U236:X237")) Is Nothing Then
For Each iChart In Array(1994)
Set cht = ActiveSheet.ChartObjects(iChart).Chart
With cht.Axes(xlValue)
.MinimumScale = ActiveSheet.Range("U237")
.MaximumScale = ActiveSheet.Range("U236")

End With
Next
End If
End Sub

 

[Andy Pope]

Hi,

It would help is you stuck to the original thread.

What chart type is it?
What are the contents of U237 and U236?

Cheers
Andy

 

[Jase]

Hey Andy,

U236 and U237 contain the x axis Max and x axis min. I applied the original
code you gave me to a dummy version to test it and it worked great. Now I am
trying the code on a different worksheet and it's giving me the error.

 

[Andy Pope]

That would suggest that the chart object on the new sheet is not index
number 1994.

Select the chartobject, then goto the immediate window in VBE. ALT+F11,
CTRL+G
and enter the following

?activechart.Parent.index

Is the value 1994?

Cheers
Andy

 

[Jase]

NICE...no it was 86.....the way i was doing it was clicking on the chart and
right clicking then going to chart window and there it displayed chart 1994.

I have about 40 charts to do this for, is this the only way to figure out
the chart #?

Also, if I want to take it off manual can I just select auto scale?


Thanks a ton Andy.

 

[Jase]

Andy, I was playing around with this code and i got all my graphs to scale
how i want them but now I was wondering it is possible to write an if
statement within the "with/end with" that allows me to have the choice to
either manually change or auto scale my graphs.

 

[Andy Pope]

Hi,

First the autoscale option. Yes use another cell, U235 for example, to
hold TRUE or FALSE for autoscaling and then use this revsion.

If Not Intersect(Target, Range("U236:X237")) Is Nothing Then
For Each iChart In Array("Chart 83")
Set cht = ActiveSheet.ChartObjects(iChart).Chart
With cht.Axes(xlValue)
If ActiveSheet.Range("U235") = True Then
.MinimumScale = ActiveSheet.Range("U237")
.MaximumScale = ActiveSheet.Range("U236")
Else
.MinimumScaleIsAuto = True
.MaximumScaleIsAuto = True
End If
End With
Next
End If

You can determine the charts name by holding the shift key when
selecting the chartobject. This will select it with white sizing handles
and deal with it as a shape. The name of the shape will be in the Name
Box, next to the formula bar. If you want to use the objects name rather
than the index then the change to the above code will work. You can even
rename the chart to something more meaningful via the name box.

The page by Jon Peltier examples linking cells to chart axes.
http://peltiertech.com/Excel/Charts/AxisScaleLinkToSheet.html

Cheers
Andy