Manipulating Charts with VBA

[Rick]

Hi All,

When I try to use the code below to change some chart properties I get various error like "
"Object doesn't support this method or property" (for the Refresh method) or the one which I can't
recall at this moment but basically says that the property can't be changed (regarding the
ChartTitle.Text property)

Sheet_6.ChartObjects(1).Visible = True
Sheet_6.ChartObjects(1).Chart.ChartTitle.Text = "Total Costs during Five Years of Analysis"
Sheet_6.ChartObjects(1).Refresh

Are these valid statements?

Rick

 

[Colleyville Alan]

Hi All,

When I try to use the code below to change some chart properties I get various error like "
"Object doesn't support this method or property" (for the Refresh method) or the one which I can't
recall at this moment but basically says that the property can't be changed (regarding the
ChartTitle.Text property)

Sheet_6.ChartObjects(1).Visible = True
Sheet_6.ChartObjects(1).Chart.ChartTitle.Text = "Total Costs during Five Years of Analysis"
Sheet_6.ChartObjects(1).Refresh

I am no expert, but from the Object Browser it appears that you are using
the wrong class for Chart.ChartTitle.Text


There are two classes listed, one singular and the other plural:
ChartObject
ChartObjects

The ChartTitle is a member of ChartObject, not ChartObjects

 

[Brian Murphy]

try DrawingObjects instead of ChartObjects, and see if that works.

Brian Murphy

 

[Tom Ogilvy]

Sheet_6.ChartObjects(1).Chart.ChartTitle.Text = "Total Costs during Five
Years of Analysis"

give an error unless you make hascharttitle = true

With Sheet_6.ChartObjects(1).Chart
.HasTitle = True
.ChartTitle.Text = "Total Costs during Five Years of Analysis"
End With

there is no refresh method for a chartobject. The chart is refreshed when a
calculate is performed.

 

[Jon Peltier]

Alan -

ChartObjects(1) refers to the first ChartObject in the collection.

Rick -

If the chart has no title, you will be unable to change its text. Use
this combination to add the title first:

With Sheet_6.ChartObjects(1).Chart
.HasChartTitle = True
.ChartTitle.Text = "Total Costs during Five Years of Analysis"
End With

You can't refresh a chartobject, just a chart:

Sheet_6.ChartObjects(1).Chart.Refresh

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Colleyville Alan]

Alan -

ChartObjects(1) refers to the first ChartObject in the collection.

Thanks

 

[Rick]

Alan -

ChartObjects(1) refers to the first ChartObject in the collection.

Rick -

If the chart has no title, you will be unable to change its text. Use
this combination to add the title first:

With Sheet_6.ChartObjects(1).Chart
.HasChartTitle = True
.ChartTitle.Text = "Total Costs during Five Years of Analysis"
End With

You can't refresh a chartobject, just a chart:

Sheet_6.ChartObjects(1).Chart.Refresh

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

Thanks everyone...and Jon you code works fine...thank you...