searching for text within shapes

[c1802362]

Hello all,

My wife has an excel workbook with an org chart created on one sheet.
The org chart corresponds to a table on an different sheet, meaning
the names in the table are also plotted on the org chart.

She wants to be able to change any shape on the org chart from a
rectangle to an oval based on criteria in the table.

My problem is that the find function in excel apparently searches
through cells and not shapes.

Does anyone have a suggestion how to search for text captured within a
shape? I didn't find any methods or properties of the shape object
that would appear to work.

Art

 

[Tim Williams]

What version of excel ?

Tim

 

[Tim Williams]

Tested in Excel 2007:

'*****************************************************************************
Sub Tester()
Dim s As Shape

Set s = FindShapeByText("hello")
If Not s Is Nothing Then
s.AutoShapeType = msoShapeOval
End If

End Sub


Function FindShapeByText(sText)
Dim rv As Shape, s As Shape

For Each s In ThisWorkbook.Sheets("Sheet1").Shapes
If s.TextFrame.Characters.Text = sText Then
Set rv = s
Exit For
End If
Next s

Set FindShapeByText = rv
End Function
'*****************************************************************************

Tim

 

[c1802362]

Tested in Excel 2007:

'************************************************************************** ***
Sub Tester()
    Dim s As Shape

    Set s = FindShapeByText("hello")
    If Not s Is Nothing Then
        s.AutoShapeType = msoShapeOval
    End If

End Sub

Function FindShapeByText(sText)
    Dim rv As Shape, s As Shape

    For Each s In ThisWorkbook.Sheets("Sheet1").Shapes
        If s.TextFrame.Characters.Text = sText Then
            Set rv = s
            Exit For
        End If
    Next s

    Set FindShapeByText = rv
End Function
'************************************************************************** ***

Tim

Thanks - I was able to incorporate the appropriate code from your
example into my code and it works perfectly!!

BTW, I'm supporting both MS Office 2003 and 2007 versions

Art