macro to list all charts on sheet

[#1]

hi all,

I'm trying to dynamically build buttons to access charts on another sheet. I am having trouble writing a macro to get a list of all of the charts on a particular sheet. I can get all of the objects, but how would I get a list of the charts and be able to access properties such as chart name, id and conditional expression for the visible property.

thanks so much,
Shanikia

[#2 · Accepted solution]

You can get the info you want by utilizing GetSheetObjects and GetObjectType (see the API Guide for more detail).

Here is an example that will display each chart Id in a message box:

Sub GetCharts
 Objects = ActiveDocument.ActiveSheet.GetSheetObjects
 For i = lBound(Objects) To uBound(Objects)
 If obj.GetObjectType = 11 'Charts Then
 msgbox("Chart: " & obj.GetObjectId)
 End If
 next
End Sub

Once you have the object Id of the chart, you will use GetProperties. Then you can manipulate the properties and use SetProperties.

[#3]

Thanks NMiller...

I thought I had the same syntax, but yours works and mine didn't so...just had to add an assignment:

For i = lBound(Objects) To uBound(Objects)

set obj = Objects(i)

If

obj.GetObjectType >= 10 Then ' All Chart types("Chart: " & obj.GetCaption.Name.v & " ID: "&obj.GetObjectId)

msgbox

End

If

next

[marcel_olmo]

Hey NMiller, thanks for your contribution.

I'm just wondering what if I would like to get all the objects of the document. Not only for the activated sheet.

How can I do that?

Many thanks in advance!!!

[#5]

In order to get all objects, I think you would need to loop through all sheets and for each sheet use something similar to the routine above. GetSheetObjects is a method of a sheet, so you can't use ActiveDocument.GetSheetObjects.

Here is an example:

Sub GetCharts
 For j = 0 to numberOfSheets //I'm not sure how to determine this
 Objects = ActiveDocument.GetSheet(j).GetSheetObjects
 For i = lBound(Objects) To uBound(Objects)
 If obj.GetObjectType = 11 'Charts Then
 msgbox("Chart: " & obj.GetObjectId)
 End If
 next
 Next
End Sub

Here, you would loop through all sheets. I didn't find a property which defined how many sheets there were in the document, but there is probably a way to figure it out.

[krishnaroopa]

set Objects = ActiveDocument.getSheet(0).GetSheetObjects

or

ActiveDocument.ActiveSheet.GetSheetObjects

Both is not working.. I am getting an error - "Object Required"

Please help

regards

KRS