Manipulating Charts with VBA

R

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
 
C

Colleyville Alan

Rick said:
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
Click to expand...


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
 
B

Brian Murphy

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

Brian Murphy
 
T

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

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/
_______
 
R

Rick

Jon said:
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/
_______
Click to expand...
Thanks everyone...and Jon you code works fine...thank you...
 
Top