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.

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


"catrrmg" <(E-Mail Removed)> wrote in message
news:E588779F-9670-4DB8-BB86-(E-Mail Removed)...
> 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.

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

"Peter T" wrote:

> 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
>
>
> "catrrmg" <(E-Mail Removed)> wrote in message
> news:E588779F-9670-4DB8-BB86-(E-Mail Removed)...
> > 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.
>
>
>