searching for text within shapes

  • Thread starter Thread starter c1802362
  • Start date Start date
C

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
 
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
 
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
Click to expand...


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
 
Back
Top