Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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.
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
Ifnext
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!!!
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.
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