chart name

[Guest]

hi all
I placed a chart through vba on a worksheet, but when I try to address to
the chart always get the same error (to rename the chart): Run-time error
'1004': Method 'Name' of object '_Chart' failed. Here is the code I'm using:
Sub AddEmbeddedChart()
Dim Chrt As Chart
ActiveSheet.ChartObjects.Delete
Set Chrt = Charts.Add
Set Chrt = Chrt.Location(where:=xlLocationAsObject, Name:="main")
With Chrt
.ChartType = xlColumnClustered
.ChartArea.Font.Name = "Tahoma"
.ChartArea.Font.FontStyle = "Regular"
.ChartArea.Font.Size = 8
.SetSourceData Source:=Sheets("data_assets").Range("b7:c24"),
PlotBy:=xlRows
.HasTitle = True
.HasLegend = False
.ChartTitle.Text = "Testing"
With .Parent
.Top = Range("F9").Top
.Left = Range("F1").Left
.Name = "GSCProductChart"
End With
End With
ActiveChart.Name = "test"
MsgBox "Chart name is " & ActiveChart.Name
Worksheets("main").Cells(1, 1).Select
End Sub
What am I doing wrong?
thanks a lot.

 

[Peter T]

Indeed you can't rename chartobject.Chart like that, but you could do say

ActiveChart.Parent.Name = "test"
(assumes no other similarly named chartobject on the sheet)

then to refer to your chart in future

Set chrt = ActiveSheet.Chartobjects("test").Chart


In passing, no need to create a chart sheet then move that to a worksheet as
a chartobject,

Set chrt = ActiveSheet.ChartObjects.Add(10, 10, 200, 150).Chart
chrt.Parent.Name = "test"
etc

Regards,
Peter T

 

[Guest]

thanks a lot. I'll give it a go.

Indeed you can't rename chartobject.Chart like that, but you could do say

ActiveChart.Parent.Name = "test"
(assumes no other similarly named chartobject on the sheet)

then to refer to your chart in future

Set chrt = ActiveSheet.Chartobjects("test").Chart


In passing, no need to create a chart sheet then move that to a worksheet as
a chartobject,

Set chrt = ActiveSheet.ChartObjects.Add(10, 10, 200, 150).Chart
chrt.Parent.Name = "test"
etc

Regards,
Peter T