How to navigate from chart shape to chart?

[WernerGg]

I create shapes in charts by

Dim theChart as Chart, theShape as Shape
Set theChart = ....
....
Set theShape = theChart.Shapes.AddShape(......)

Those shapes are clearly some sort of child of the chart. E.g. they cannot be positioned out of the ChartArea.

Later I have to navigate from such a shape back to the containing chart. Unfortunatly the shapes parent is not the chart but the worksheet and I can find no other shape property to get back to the chart. Currently I use a brute force method by naming the shapes such that they can be identified.

Is there a clean method to find the Chart from the Shape?

 

[VoG]

If these are embedded charts try ChartObject(...).Shapes(...)

 

[WernerGg]

If these are embedded charts try ChartObject(...).Shapes(...)

Maybe you misunderstood. I want to navigate FROM a known shape TO the containing chart.

 

[Andy Pope]

You need to step up twice

Sub X()

    Dim chtTemp As Chart
    Dim shpTemp As Shape
    
    Set chtTemp = ActiveSheet.ChartObjects(1).Chart
    Set shpTemp = chtTemp.Shapes.AddShape(msoShape5pointStar, 1, 1, 30, 30)
    
    MsgBox shpTemp.Name                    ' 5-Point Star 4
    MsgBox shpTemp.Parent.Name             ' Sheet1 Chart 1
    MsgBox shpTemp.Parent.Parent.Name      ' Chart 1
    
End Sub

 

[WernerGg]

You need to step up twice ....

Thanks Andy for your reply. Of course your program shows what everybody expects (shpTemp.Parent = chtTemp - one step, not two). But I am still confused.

I understand now, what my real problem is, but I still have no solution. The point is that a user selects the created shape and my program has to detect the chart from that selection. So I don't have the shape at first but only the selection and - pretty strange - this selection is NOT the shape but something with Typename "Rectangle", i.e. an unknown, at least undocumented object .

Look at the following code and run it on a worksheet with an embedded chart and on a pure chart-sheet:

Sub X()
' Create a shape in a chart and detect its parents
    Dim theChart As Chart, theShape As Shape, anyObj As Object
 
    Debug.Print
    Debug.Print "--- find the first chart of active worksheet or chart-sheet"
    On Error Resume Next
    Set theChart = ActiveSheet.ChartObjects(1).Chart ' assume its imbedded
    On Error GoTo 0
    If theChart Is Nothing Then Set theChart = ActiveChart ' chart-sheet
    If TypeName(theChart) <> "Chart" Then Exit Sub
 
    Debug.Print "--- create a shape within that chart"
    Set theShape = theChart.Shapes.AddShape(msoShape5pointStar, 1, 1, 30, 30)
 
    Debug.Print "--- print the shape with its parents"
    DebugPrint_ObjParents theShape
    Debug.Print "==> This is true: theShape.Parent = theChart"
 
    Debug.Print "--- select the shape and print the selection with its parents"
    theShape.Select
    Set anyObj = Selection  ' one would expect that anyObj = theShape
                            ' but it is not:
    DebugPrint_ObjParents anyObj
    Debug.Print "==> This is in general NOT true: Selection.Parent = theChart"
    Debug.Print "    Its true only on a chart-sheet, but not on a worksheet"
    Debug.Print "The reason is that after ""anyObj.Select"" ""Selection"" is NOT equal with ""anyObj"""
 
    Debug.Print "--- delete the shape"
    theShape.Delete
 
End Sub
Public Sub DebugPrint_ObjParents(theObj As Object)
    On Error GoTo Done
    Debug.Print "theObj:                "; _
        TypeName(theObj), theObj.Name
    Debug.Print ".Parent:               "; _
        TypeName(theObj.Parent), theObj.Parent.Name
    Debug.Print ".Parent.Parent:        "; _
        TypeName(theObj.Parent.Parent), theObj.Parent.Parent.Name
    Debug.Print ".Parent.Parent.Parent: "; _
        TypeName(theObj.Parent.Parent.Parent), theObj.Parent.Parent.Parent.Name
Done:
End Sub

The output on a worksheet is:

--- find the first chart of active worksheet or chart-sheet
--- create a shape within that chart
--- print the shape with its parents
theObj:                Shape              Stern mit 5 Zacken 12
.Parent:               Chart              TestCD C_A
.Parent.Parent:        ChartObject        C_A
.Parent.Parent.Parent: Worksheet          TestCD
==> This is true: theShape.Parent = theChart
--- select the shape and print the selection with its parents
theObj:                Rectangle          Stern mit 5 Zacken 12
.Parent:               Worksheet          TestCD
.Parent.Parent:        Workbook           Test gizDCo.xlsb
.Parent.Parent.Parent: Application        Microsoft Excel
==> This is in general NOT true: Selection.Parent = theChart
    Its true only on a chart-sheet, but not on a worksheet
The reason is that after "anyObj.Select" "Selection" is NOT equal with "anyObj"
--- delete the shape

But on a chart-sheet you get what one would expect:

--- find the first chart of active worksheet or chart-sheet
--- create a shape within that chart
--- print the shape with its parents
theObj:                Shape              Stern mit 5 Zacken 9
.Parent:               Chart              YB Sheet
.Parent.Parent:        Workbook           Test gizDCo.xlsb
.Parent.Parent.Parent: Application        Microsoft Excel
==> This is true: theShape.Parent = theChart
--- select the shape and print the selection with its parents
theObj:                Rectangle          Stern mit 5 Zacken 9
.Parent:               Chart              YB Sheet
.Parent.Parent:        Workbook           Test gizDCo.xlsb
.Parent.Parent.Parent: Application        Microsoft Excel
==> This is in general NOT true: Selection.Parent = theChart
    Its true only on a chart-sheet, but not on a worksheet
The reason is that after "anyObj.Select" "Selection" is NOT equal with "anyObj"
--- delete the shape

Isn't that strange? No idea how I can navigate from the Selection to the shape and chart.

Thanks for your interest.

 

[WernerGg]

I forgot to mention that I was working on Excel 2007. I hoped this was just another of these weird bugs in xl2007.

Now I run the test on Excel 2010. It is exactly the same.

 

[Andy Pope]

The selection object does behave differently to that of xl2003. Previously you would have got 'Sheet1 Chart 1' for the selection object parent.

Maybe you can use this to deal with the variation.

    If Not ActiveChart Is Nothing Then
        ' assume shape is within a chart
        DebugPrint_ObjParents ActiveChart.Shapes(Selection.Name)
    End If

 

[WernerGg]

The selection object does behave differently to that of xl2003. Previously you would have got 'Sheet1 Chart 1' for the selection object parent.

Maybe you can use this to deal with the variation.

    If Not ActiveChart Is Nothing Then
        ' assume shape is within a chart
        DebugPrint_ObjParents ActiveChart.Shapes(Selection.Name)
    End If

OK. Thanks for that version information. Sometimes they drive us crazy these MS programmers. Incredible things happen and fundamental issues are changed from version to version. Just for fun: Select an axis and try "DebugPrint_ObjParents Selection". You will not get to Chart in one step as expected but into a loop Axis->Gridlines->Axis->Gridlines->....

For your variation: Yes, I am doing this kind of stuff, searching by name for the shapes. What I called "brute force" in my first post.

If somebody else knows an answer to: "How to find anObj back again from Application.Selection after anObj.Select", I would be glad.

 

[WernerGg]

Just for clearness: DebugPrint_ObjParents will of course not loop because it has only four steps.

In reality I used a little subroutine DebugPrint_Parents(anObj As Object) which travels the parent chain of any object:

Public Function DebugPrint_Parents(anObj As Object, _
                    Optional quiet As Boolean = False) As String
' Print the type of anObj and its parents
' The text is returned as well.
' If quiet nothing is printed.
    Dim myName As String, myObj As Object
    Dim myType As String, cnt As Integer, str As String, isTop As Boolean
    myName = "DebugPrint_Parents"
    str = ""
    Set myObj = anObj
    cnt = 0: isTop = False
    Do While (Not myObj Is Nothing) And cnt < 10
        myType = TypeName(myObj)
        If cnt > 0 Then str = str & vbNewLine
        str = str & Space(cnt * 1) & "Type: " & myType
        On Error Resume Next
        str = str & " (Name: " & myObj.Name & ")"
        On Error GoTo 0
        Set myObj = myObj.Parent
        If myType = "Application" Then isTop = True: Exit Do
        cnt = cnt + 1
    Loop
    If (Not myObj Is Nothing) And (Not isTop) Then
        str = str & vbNewLine & Space(cnt * 1) & "..."
    End If
    DebugPrint_Parents = str
    If Not quiet Then
        Debug.Print myName & ":" & vbNewLine & str
    End If
End Function

This produces a string like:

DebugPrint_Parents:
Type: LegendEntry
 Type: Legend (Name: Legende)
  Type: Chart (Name: TestCD C_Y)
   Type: ChartObject (Name: C_Y)
    Type: Worksheet (Name: TestCD)
     Type: Workbook (Name: Test gizDCo.xlsb)
      Type: Application (Name: Microsoft Excel)