在excel中经常遇到有用户使用图相框或者文本框来输入内容,使excel看上去更加美观,但是如果我们想查找的文字内容在文本框或者图相框中,那么正常的ctrl+F的查找功能就不能正常索搜到这些文字。
需要使用vba来实现索搜图相框或者文本框中的内容,下面是基本的实现代码。
Function findInShape(str As String, Optional ByVal sel As Boolean = False) As String()
Dim strret(1) As String
Dim strget, strtmp, strdd As String
strret(0) = ""
strret(1) = ""
strtmp = ""
strget = ""
For Each ws In ThisWorkbook.Worksheets
' If ws.Index < 4 Then
' GoTo ConOuter
' End If
For Each sh In ws.Shapes
'
strshow = strshow + sh.AlternativeText
On Error GoTo ConInner
cnt = cnt + 1
'whether the shape type is AutoShape
If sh.Type = msoAutoShape Then
'key process: get description from this AutoShape
'in order to skip line shape
On Error Resume Next
' If Len(sh.AlternativeText) > 0 Then
strget = sh.TextFrame.Characters.Caption
If InStr(strget, str) <> 0 Then
strtmp = sh.TopLeftCell.Address(False, False)
If sel Then
'Ok , activate it!
ws.Activate
sh.TopLeftCell.Select
sh.TopLeftCell.Activate
'continue ?
If MsgBox(ws.Name + "!" + strtmp, vbOKCancel, "continue search?") = vbCancel Then
Exit Function
End If
End If
strret(0) = strret(0) + strget + ";"
strret(1) = strret(1) + ws.Name + "!" + strtmp + ";"
'Exit For
End If
' End If
End If
ConInner:
Next
ConOuter:
Next
If Len(strret(0)) > 0 Then
strret(0) = Left(strret(0), Len(strret(0)) - 1)
strret(1) = Left(strret(1), Len(strret(1)) - 1)
Else
strret(0) = "-"
strret(1) = "-"
End If
findInShape = strret
End Function