Chart.Name lives forever?

  • Thread starter Thread starter cate
  • Start date Start date
C

cate

At the beginning of the code I blow away any chart objects I find with

Dim co As ChartObject
For Each co In mySheet.ChartObjects
co.Delete
Next

And for good measure (this is probably all you have to do)

mySheet.ChartObjects.Delete

Afterwards, Chart.ChartObjects.Count is == 0

Later, I review the charts on the sheet with

For Each co In ws.ChartObjects
sTitle = co.Chart.Name
MsgBox ("has the name " & sTitle)
Next

Only one chart is found, but it's name is incrementing. This leads me
to believe that I really didn't blow away everything and that I can't
be sure I'm starting with a clean slate.

If I delete the sheet and recreate it, the chart names reset.

Why does this name persist?
 
At first blush, I wonder if you really do "blow them away". Add this to
your code

Try this

dim myWS as excel.worksheet
Dim co as Excel.chartojbect
set myWS = ActiveSheet

debug.print "Before: " & myWS.ChartObjects.Count

for each co in myWS.Chartobjects
co.delete
next co

debug.print "After: " & myWS.Chartobjects.Count

HTH,

Barb Reinhardt
 
Hi cate,

The numeric suffix of the chart names continues to increment until you close
the workbook and re-open it. If you create 3 charts in a freshly opened
workbook then their names will be Chart 1, Chart 2 and chart 3.

If you then delete them all and create another one before closing the
workbook, then it will be Chart 4.

I am suspecting that your question relates to being able to reference the
charts after they are created and if so, you need to assign names to the
charts at the time of creating.

The following code example creates a charts and names it. If you are
creating several charts then you can use a loop and concatenate "Chart" with
the loop variable for the chart name or you might even want to give them a
more meaningful name.


Sub CreateCharts()
Dim rngChrtPos As Range
Dim ws As Worksheet
Dim chrtObj As ChartObject

Set ws = Sheets("Sheet1")
Set rngChrtPos = ws.Range("D2:G12")

With rngChrtPos
Set chrtObj = ws.ChartObjects.Add _
(.Left, .Top, .Width, .Height)

chrtObj.Name = "Chart 1"

End With

With chrtObj.Chart
.SetSourceData ws.Range("A1:B14")
.ChartType = xlLineMarkers
End With

End Sub
 
Hi cate,

The numeric suffix of the chart names continues to increment until you close
the workbook and re-open it. If you create 3 charts in a freshly opened
workbook then their names will be Chart 1, Chart 2 and chart 3.

If you then delete them all and create another one before closing the
workbook, then it will be Chart 4.

I am suspecting that your question relates to being able to reference the
charts after they are created and if so, you need to assign names to the
charts at the time of creating.

The following code example creates a charts and names it. If you are
creating several charts then you can use a loop and concatenate "Chart" with
the loop variable for the chart name or you might even want to give them a
more meaningful name.

Sub CreateCharts()
Dim rngChrtPos As Range
Dim ws As Worksheet
Dim chrtObj As ChartObject

Set ws = Sheets("Sheet1")
Set rngChrtPos = ws.Range("D2:G12")

With rngChrtPos
  Set chrtObj = ws.ChartObjects.Add _
      (.Left, .Top, .Width, .Height)

  chrtObj.Name = "Chart 1"

End With

With chrtObj.Chart
  .SetSourceData ws.Range("A1:B14")
  .ChartType = xlLineMarkers
End With

End Sub
Click to expand...

thank you. And you suspect correctly.
 
Back
Top