excel - vba: test if shape contains a textframe

Question

I want to loop over all shapes in a chart in an excel file. This works in principle with

Dim currChart As Chart
Set currChart = Sheets("Diagramm 1")

Dim sShapes As Shape
For Each sShapes In currChart.Shapes

        Debug.Print sShapes.name
        Debug.Print sShapes.TextFrame.Characters.Text

Next sShapes

However, the property TextFrame is not known by all type of shapes. therefore I want to test if a shape has a textframe. How can I do that?

Answer 1

I assumed that you need to know if there is text within your shape object. Therefore try to put this code within your loop For...Next:

Debug.Print sShapes.Name
'to check if there is textframe
'but mostly there is
If Not sShapes.TextFrame Is Nothing Then

    'to check if there is text within textframe
    If sShapes.TextFrame2.HasText Then

        Debug.Print sShapes.TextFrame.Characters.Text
    End If
End If

I hope it is what you are looking for.