Confusion about giving a chart a title

[chamster]

I run this code.

Dim ac As Chart
Charts.Add
Set ac = ActiveChart
ac.HasTitle = True
ac.Location Where:=xlLocationAsObject, Name:=.Name
ac.Parent.Name = "Poo on you"


The placement is OK but as i try to change the name, i get errors. I've read somewhere that i can't set name directly to the ac but i should do so to it's parent. Doesn't seem to be working...

[tpoynton]

try this:


Dim ac As Chart
Charts.Add
Set ac = ActiveChart
ac.HasTitle = True
ac.ChartTitle.Characters.Text = "Poo on you"

[chamster]

Dim ac As Chart
Charts.Add
Set ac = ActiveChart
ac.HasTitle = True
ac.ChartTitle.Characters.Text = "Poo on you"

Is this the recommended way? I believe i saw something about the parents name to be the right approach. Did i misunderstand, perhaps?

[tpoynton]

I'm no expert on best practice...I'm not even an expert. However, at the least, that shouldnt throw errors.

[Andy Pope]

It is a little unclear what exactly you want to do.

The code below will change the chart title, chart location, chartobjects name and finally the sheet name.
Note the active cell should be in an area containing valid data as the chart title will cause an error is the chart has no data series.

Sub x()

Dim ac As Chart
Charts.Add
Set ac = ActiveChart
ac.HasTitle = True
ac.ChartTitle.Text = "My chart title"
' move to sheet1
ac.Location Where:=xlLocationAsObject, Name:=Sheet1.Name
' re reference activechart otherwise Automation error
Set ac = ActiveChart
' change name of chart object
ac.Parent.Name = "poo on you"
' change name of sheet containing activechart
ac.Parent.Parent.Name = "MySheet"

End Sub

[chamster]

It is a little unclear what exactly you want to do.

The code below will change the chart title, chart location, chartobjects name and finally the sheet name.
Note the active cell should be in an area containing valid data as the chart title will cause an error is the chart has no data series.

Sub x()

Dim ac As Chart
Charts.Add
Set ac = ActiveChart
ac.HasTitle = True
ac.ChartTitle.Text = "My chart title"
' move to sheet1
ac.Location Where:=xlLocationAsObject, Name:=Sheet1.Name
' re reference activechart otherwise Automation error
Set ac = ActiveChart
' change name of chart object
ac.Parent.Name = "poo on you"
' change name of sheet containing activechart
ac.Parent.Parent.Name = "MySheet"

End Sub


I believe i see what the problem was (at least in some cases). I see you need to re-reference the chart. What i wonder is why. To me, it looks like we've got us a pointer to the chart wherever it is. Obviously, that is errorneous but i wish to understand why it is so.

Thanks for the example, by the way. Appreciated!

[Andy Pope]

Can not give you a definitive answer as I don't know, but it would appear the object referencing is not maintained when converting a chart sheet to a chart object.

The following code will take a chartobject and convert to a chartsheet without loss. But going the other way causes a failure. So use with care!

Make sure your test chart has a chart title.

Sub Macro1()
'
' USE WITH CAUTION AS IT CAN CRASH EXCEL
'
Dim chtTemp As Chart
'
Set chtTemp = ActiveSheet.ChartObjects(1).Chart
Debug.Print "Chart Object ", chtTemp.ChartTitle.Text

ActiveChart.Location Where:=xlLocationAsNewSheet
Debug.Print "Chart sheet ", chtTemp.ChartTitle.Text

' CAN CRASH EXCEL
chtTemp.Location Where:=xlLocationAsObject, Name:="Sheet1"
Debug.Print "Chart object again ", chtTemp.ChartTitle.Text

End Sub

[johnske]

Can not give you a definitive answer as I don't know, but it would appear the object referencing is not maintained when converting a chart sheet to a chart object.what Andy says is most likely spot on, you can compare it with...

Sub AddChart()
'
Dim SheetName As String
'
With ActiveSheet
SheetName = .Name
.ChartObjects.Delete 'delete any pre-existing charts
End With
'
'Create a new chart and move to active sheet (SheetName)
With Charts.Add.Location(Where:=xlLocationAsObject, Name:=SheetName)
.ChartType = xlLine 'or xlColumnClustered...etc.
'Set data source range.
.SetSourceData _
Source:=Sheets(SheetName).Range("B2:B25"), PlotBy:=xlColumns
.HasTitle = True
.ChartTitle.Text = "My chart title for " & SheetName
'The Parent property is used to set properties of the Chart.
With .Parent
.Name = "MyDataChart"
.Top = .Parent.Range("F9").Top
.Left = .Parent.Range("F9").Left
End With
End With
'
End Sub