Find out if Shape has Text

[a1s2d3f4]

Have an .xls file with multiple sheets and many objects (Shapes) on of those sheets.
The objects are all rectangles, some of which contain text frames, where I had entered manually some text.

Also, each shape has a name.

Now, I want my VBA Macro to take a specific shape (using its name), and find out if it has text frame in it.

i thought the way to do it is

Set oShp = ActiveSheet.Shapes(X)
If oShp.HasTextFrame Then  
   Msgbox oShp & "has text frame"
End If

,where X is the string that is the name of the shape I am testing.

Every example I could find had that type of code above.
But in my case I get "Object doesn't support this property or method" when I call this code.

Also, scanning through Shapes Collection properties, I don't actually see "HasTextFrame" mentioned. Only "HasDiagram".

Would anyone have any suggestions?

Thanks,
a1

[JBeaucaire]

Everything I've read about that property indicates it is a PowerPoint function.

[StephenR]

Not sure, but you could try using the AlternativeText property. It appears by default to be populated with any text in the shape, but I don't know whether this is a hard and fast rule.

Sub x()

Dim s As Shape

For Each s In ActiveSheet.Shapes
    If s.AlternativeText <> "" Then MsgBox s.Name & " has text"
Next s

End Sub

[Krishnakumar]

May be...

Set oShp = ActiveSheet.Shapes(x)
If oShp.TextFrame.Characters.Count Then
   MsgBox oShp.Name & "has text frame"
End If

[Andy Pope]

Looks like the .Characters approach will only work if the shape has had text edited within it, even if it now contains no text.

So the best approach would be a function that use the character count method but also contains error trapping

[Krishnakumar]

Thanks Andy for the input. So may be function like this..

Function HASTEXTINSHAPE(shpObj) As Boolean
HASTEXTINSHAPE = False
On Error Resume Next
HASTEXTINSHAPE = Len(shpObj.TextFrame.Characters.Count)
On Error GoTo 0
End Function

would work

[a1s2d3f4]

Thanks to all the replies.

I could get StephenR's to work.

When i tried Krishnakumar's

Function HASTEXTINSHAPE(shpObj) As Boolean
HASTEXTINSHAPE = False
On Error Resume Next
HASTEXTINSHAPE = Len(shpObj.TextFrame.Characters.Count)
On Error GoTo 0
End Function

I couldn't get it to work, but maybe I did something wrong.
As a test, I stuck it into a separate Module in my View Code window.
Then, I made a test Sub Macro2() to call it.
i.e.

Sub Macro2()

    x = "Rectangle 168"
    If HASTEXTINSHAPE(x) Then
        MsgBox x & " has text"
    Else
        MsgBox x & " has no text"
    End If

End Sub


Function HASTEXTINSHAPE(shpObj) As Boolean
    HASTEXTINSHAPE = False
    On Error Resume Next
    HASTEXTINSHAPE = Len(shpObj.TextFrame.Characters.Count)
    On Error GoTo 0
End Function

But when I tried running it, I always got "Rectangle 168 has no text", even if I explicitly entered some text there.

Since VBA is far from being my second language, I am sure I am doing something wrong here.

Thanks,
a1

[romperstomper]

Change the code to:

Sub Macro2()

    Set x = Activesheet.Shapes("Rectangle 168")
    If HASTEXTINSHAPE(x) Then
        MsgBox x.Name & " has text"
    Else
        MsgBox x.Name & " has no text"
    End If

End Sub

[StephenR]

That doesn't appear to get round the deleted text point made by Andy.

[romperstomper]

I've used:

Function ShapeHasText(shp as Shape) As Boolean
    On Error Resume Next
    ShapeHasText = (Len(shp.TextFrame.Characters.Text) > 0)
End Function

before.

[a1s2d3f4]

Ok, just to report:
thanks, Romperstomper, for pointing out that I was sending a STRING! to the HASTEXTINSHAPE. That was silly of me.
So, I did change the code to this:

Sub Macro2()
    Dim x As Shape
    
    Set x = ActiveSheet.Shapes("Rectangle 168")
    
    If HASTEXTINSHAPE(x) <> False Then
        MsgBox x.Name & " has text"
    Else
        MsgBox x.Name & " has no text"
    End If
    
End Sub

Function HASTEXTINSHAPE(shpObj As Object) As Boolean
    HASTEXTINSHAPE = False
    On Error Resume Next
    HASTEXTINSHAPE = Len(shpObj.TextFrame.Characters.Count)
    On Error GoTo 0
End Function

and everything worked correctly.

If I make a fresh shape, and execute the Macro, it has no text, and so the HASTEXTINSHAPE returns "no text". If I Add Text to it, even without typing any characters in the box, it return "there is text".

Obviously, if I have Alternative Text associated with that shape, this function is not affected.

Interestingly enough, when I tried to declare shpObj "as Shape" inside HASTEXTINSHAPE, it didn't like it at all.

This is the error box:

---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

Variable required - can't assign to this expression
---------------------------
OK Help
---------------------------



But when I declared it as "Object", it was happy.

That's the only puzzling thing for me, since clearly it's a "Shape".



I am going to mark this as SOLVED since I have two different solutions here that approach this issue from two different angles.
EDIT: Hmm, can't see a way to mark it as "SOLVED". Should I worry about this?


*If someone uses Alternative Text method, make sure that you really don't have anything in the Alternative Text box (Format Autoshape), or this is not going to work.



Thanks,
a1

[Andy Pope]

The error when declaring as shape is due to the incorrect usage of Len/Count.
Once the compiler knows what the object is it can object

' so either just use the count
    HASTEXTINSHAPE = shpObj.TextFrame.Characters.Count

' or the len of the text
    HASTEXTINSHAPE = Len(shpObj.TextFrame.Characters.text)

I will mark the post as solved. There is a time limit for users.