How to search within a text box?

[arrun]

Dear all, I have an excel file (excel 2007) where there are lot of text boxes and each text box contains lot of information like, number, english word etc. I want to have some mechanism to search a specific word within those textboxes. Usual Excel way like cont+F does not work is desired word is within text box. Does anybody know any way how to extend that cont+F, so that I can be able to search within any textbox?

Thanks for your help.

[Chabu]

What do you mean with "text boxes"? Cell comments?

[Kenneth Hobs]

If an ActiveX textbox then InStr will not be 0 if the word if found in the value property.

Dim oo As OLEObject
Sheet1.TextBox1.Value = "Mary had a little lamb."
For Each oo In Sheet1.OLEObjects
If Left(oo.Name, 7) = "TextBox" Then MsgBox oo.Object.Value, , InStr(oo.Object.Value, "little")
Next oo

[arrun]

Hi Kenneth, thanks for your code. Can you please tell me how I use that code?

Regards,

[Kenneth Hobs]

How you use it depends on what you want to do. Half the work to solve a problem or achieve a goal is in the definition. You said that you wanted to search but search for what and then what to do if found?

[arrun]

Hi Kenneth, I agree, I must tell more. I just want to find the places where a particular word is placed in many text-boxes in a particular sheet. My goal is, just to find, nothing else.

Thanks,

[Kenneth Hobs]

Here is another example where if looks in all sheets and textbox controls. Notice that this example and the previous one set the value on Sheet1 for Textbox1. Add that control and then some others and test. If the Instr result is not 0, it was found. There is a compare option in Instr() that you can use if needed.

Sub t()
Dim oo As OLEObject, ws As Worksheet
Sheet1.TextBox1.Value = "Mary had a little lamb."

For Each ws In Worksheets
For Each oo In ws.OLEObjects
With ws
If Left(oo.Name, 7) = "TextBox" Then
MsgBox "TextBox Name: " & oo.Name, vbInformation, "Worksheet Name: " & _
oo.Parent.Name
MsgBox "Value: " & oo.Object.Value, vbInformation, "Position: " & _
InStr(oo.Object.Value, "little")
End If
End With
Next oo
Next ws
End Sub