Finding Chart Object Names

[svg109]

Hello.

I have a big file with multiple sheets, each containing multiple charts.

By default, Excel names the chart objects ( 1 to n) in a sheet.

I need to create a list of all the chart objects in my workbook. How should I do this? i.e. a list that says : - Sheets(i), chartobjects("Chart 1")...and so on.

Basically, I am dynamically generating a different number of charts in a sheet, depending on my data, and I need to find out how many charts are contained in the workbook at the end. Potentially, I could use a variable in my code that keeps track of charts generated, but I would like to find the number of charts for previous files as well without going through the loop all over again.

To make things more interesting: What would happen if i grouped 4 charts together? How can i find out the group name through VBA?

All of this is part of a bigger code, where I am copy pasting(special) all these charts to generate a word document.

Any help would be extremely appreciated.

Thanks in advance
SG

 

[Damon Ostrander]

Hi SG,

The following macro illustrates how to list all the embedded chart objects in your workbook.

Sub ListCharts()

'List all embedded charts in workbook
Dim Clist As String
Dim WSname As String * 18
Dim WS As Worksheet
Dim CO As ChartObject
Dim TotalCOs As Integer

WSname = "WORKSHEET"
Clist = WSname & vbTab & "CHART OBJECT NAME"

For Each WS In Worksheets
For Each CO In WS.ChartObjects
WSname = WS.Name
Clist = Clist & vbCr & WSname & vbTab & CO.Name
TotalCOs = TotalCOs + 1
Next CO
Next WS

MsgBox Clist, vbInformation, TotalCOs & " Chart Objects Found"

End Sub


Regarding your question about grouped charts, listing these requires looping through all the shapes in the workbook and checking their Type property, which will have a value of 6 (or msoGroup) if the shape is a Group of objects. Then you must loop through the objects within the group to determine which ones are chart objects.

I hope this helps.

Damon

 

[svg109]

Damon,

That was brilliant! It sure does help big time!

Have another quick question for you:
When I create a new chart in a worksheet, Excel names it sequentially in the order it was created (chart 1 , chart 2 ...and so on). Even if I delete 2 charts and re-create them, they get named as Chart 3, chart 4.
How can I force Excel to begin at Chart 1 each time?

Or how do I physically change the chart object name from Chart 10 to Chart 1?

Also in your code, I noticed that you have dimensioned WSname as string *18. What does the *18 do? I have not had a chance to see/use this ever before.

Thank you very much for your help!

SG

 

[Damon Ostrander]

Hi svg18,

Regarding your first question, here is an example of creating a chart and assigning the name upon creation. Notice that I use the Parent property of the chart to get the ChartObject which contains the Chart, and it is the ChartObject which is getting named.

Sub MakeChart()

' Macro to demonstrate creation and naming of embedded chart
' author: Damon Ostrander
' mailto: VBAexpert@piadamon.com
' Date: 5/9/2001

Dim MyChart As Object
Charts.Add
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B21"), PlotBy _
:=xlColumns
Set MyChart = ActiveChart.Location(Where:=xlLocationAsObject, Name:="Sheet1")
With MyChart
.Parent.Name = "DamonsChart"
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

But if you want to name ChartObjects that were created previously (perhaps even manually) you can use code such as I provided before and simply setting the CO.Name property to a text string.

Regarding WSname As String*18, this declares WSname to be an 18-character string, so that if for example you use an assignment like:

WSname = "Hello"

WSname actually contains "Hello ", or Hello followed by 13 spaces. I used this simply to ensure that the text being printed in the MsgBox was always exactly 18 characters for text alignment purposes.

 

[JimmyM1975]

Hi SG,

The following macro illustrates how to list all the embedded chart objects in your workbook.

Sub ListCharts()

'List all embedded charts in workbook
Dim Clist As String
Dim WSname As String * 18
Dim WS As Worksheet
Dim CO As ChartObject
Dim TotalCOs As Integer

WSname = "WORKSHEET"
Clist = WSname & vbTab & "CHART OBJECT NAME"

For Each WS In Worksheets
For Each CO In WS.ChartObjects
WSname = WS.Name
Clist = Clist & vbCr & WSname & vbTab & CO.Name
TotalCOs = TotalCOs + 1
Next CO
Next WS

MsgBox Clist, vbInformation, TotalCOs & " Chart Objects Found"

End Sub


Regarding your question about grouped charts, listing these requires looping through all the shapes in the workbook and checking their Type property, which will have a value of 6 (or msoGroup) if the shape is a Group of objects. Then you must loop through the objects within the group to determine which ones are chart objects.

I hope this helps.

Damon

This is great but can someone help me display the list as a NEW SHEET and not as a MsgBox because I have 3,000 charts and the information being returned is much bigger than the space limit (1,024 ch) of the MsgBox?