help with chart objects

[inquirer]

Could someone tell me what I am doing wrong please in this code:

'Dim myChart As Chart

wsheet = ActiveSheet.Name

Charts.Add
cname = ActiveChart.Name
'Set myChart = ActiveChart

ActiveChart.ChartType = xlLineMarkers

ActiveChart.SetSourceData
Source:=Sheets("aulogbd").Range("C2:C23,E2:E23"), _
PlotBy:=xlColumns
ActiveChart.SeriesCollection(1).Delete
ActiveChart.SeriesCollection(1).XValues = "=aulogbd!R2C3:R23C3"
ActiveChart.SeriesCollection(1).Name = "=""0,0"""
ActiveChart.Location Where:=xlLocationAsObject, Name:="aulogbd"
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
more code
'myChart.Activate
Worksheets(wsheet).ChartObjects(cname).Activate

When I run this I get an error Unable to get the ChartObjects of the
worksheet class at the last line. If I use the myChart logic (Remmed out
here) instead of cname = ActiveChart.Name and
Worksheets(wsheet).ChartObjects(cname).Activate , I get an Automation error.

What I want to do is to create a chart and then go back to that chart at a
later point and add more data to it.

Thanks
Chris

 

[Sean]

The reason you cannot use the chart name is that it changes when you use
the .Location method. The code below should give you an indication of
what happens.
Step (2) uses the fact that your chart MUST be the last created
chartobject to set the reference WITHOUT having to actually know it's
name
In step (3) you could add the Name:= parameter to force your chartsheet
back to it's original name, just remember to check that there is not
another sheet with the same name


----
Sean
"Just press the off switch, and go to sleep!"

'----------------------------------------------------
'----------------------------------------------------
Sub ChartNameChanging()
Dim myChart As Chart, msg As String

' (1) start with a simple chartsheet
Set myChart = ActiveChart
msg = msg & "Original Chartsheet = " & myChart.Name & vbCrLf

' (2) move it to an embedded object on a worksheet
myChart.Location Where:=xlLocationAsObject, Name:="aulogbd"
Set myChart =
Worksheets("aulogbd").ChartObjects(Worksheets("aulogbd").ChartObjects.Co
unt).Chart
msg = msg & "Now embedded chart = " & myChart.Name & vbCrLf

' (3) move it back to a chartsheet
myChart.Location Where:=xlLocationAsNewSheet
Set myChart = ActiveChart
msg = msg & "Back to Chartsheet = " & myChart.Name & vbCrLf


MsgBox (msg)
Set myChart = Nothing
End Sub
'----------------------------------------------------
'----------------------------------------------------