VBA and Charts: best way to test for Chart vs. ChartObject

**tcarnahan** · 08-21-2015, 11:42 AM

The Chart/ChartObject object model is confusing. I have recorded macros for a chart and the code refers to the ActiveChart. When I tried writing code to go through all the tabs of the workbook, ActiveChart fails on some tabs.

I have since learned that there are "Chart Sheets" with one chart which is the active chart. Then there are worksheets with one or more ChartObjects, which may or may not have the focus, and thus may or may not be an ActiveChart.

Because of this distinction, I have to write code for two different situations, Chart vs. ChartObject.

Is there a "best practice" way of iterating through the tabs of the workbook and testing to see if the tab is a Chart Sheet or a Worksheet with one or more Chart Objects?

Thanks,

Tom

**Andy Pope** · 08-22-2015, 04:43 AM

Activechart is the chart that is active, is currently selected if a object or the tab if a chart sheet.
There can only be a maximum of 1 activechart. And if no chart is selected it references nothing.

in the example file are chart sheets, chart objects. With chart objects both on worksheets and chart sheets.

this code will process them

Sub X()
    Dim Sht As Worksheet
    Dim Cht As Chart
    Dim ChtObj As ChartObject
    Dim ChtOnSheet As Chart
    
    For Each Cht In ActiveWorkbook.Charts
        Debug.Print "Chart Sheet " & Cht.Name
        For Each ChtObj In Cht.ChartObjects
            Set ChtOnSheet = ChtObj.Chart
            Debug.Print , "Chart object", ChtObj.Name, "Chart name", ChtOnSheet.Name
        Next
    Next
    
    For Each Sht In ActiveWorkbook.Worksheets
        Debug.Print "Chart object on worksheet", Sht.Name
        For Each ChtObj In Sht.ChartObjects
            Set ChtOnSheet = ChtObj.Chart
            Debug.Print , "Chart object", ChtObj.Name, "Chart name", ChtOnSheet.Name
        Next
    Next
    
End Sub