How can I search for text within text boxes?

[cQuinc]

I have an Excel sheet with over 1000 text boxes filled with text. I would like to find a way to search all the text boxes for words or numbers. Is this possible?

Quin

 

[pedie]

Hi Quin, welcome to forum.

try this code and modify it the way you want to...

[/FONT]
[FONT=Courier New]Sub LoopMyTextboxes()
Dim oleObj As OLEObject
Dim x[/FONT]
[FONT=Courier New]x = InputBox("What would you like to search in TEXTBOXES?")[/FONT]

[FONT=Courier New]For Each oleObj In ActiveSheet.OLEObjects
If TypeOf oleObj.Object Is MSForms.TextBox Then
 If oleObj.Object.Value = x Then
  MsgBox "It contain " & oleObj.Object.Value
 End If
End If
Next
End Sub

 

[cQuinc]

Thanks, I am always impressed when someone is willing to write some code for me. I have a couple of questions before I start.

The value of "X", do I place it in quotes for text and no quotes for numbers? Also I would like a way to identify the text box that contains the desired text. If I understand your code I think the result of running your VBA would be to create a text box that repeats the value (word or number) I am looking for.

Instead, I would like an output that gives the name of the text box so I can find it. Something like "Text Box 101" or maybe there is a better way to find or go to the text box that has the text.​

 

[pedie]

It basically looks for x you will input in inputbox without ""
If it contains then you can enter your statement in

[FONT=Courier New]If oleObj.Object.Value = x Then[/FONT]
[FONT=Courier New][B]  'your statement here...what do you want the code to do if 'x value is found in textbox[/B][/FONT]
[FONT=Courier New]End If[/FONT]

You may enter words, number etc in inputbox without ""
And then vba check for textbox type on all object in activesheet and then give you result that you'll provide yourself or tel me what you want to do if found...

 

[Nalani]

How about this little modification to pedie's code:

Sub LoopMyTextboxes()
Dim oleObj As OLEObject
Dim x
x = InputBox("What would you like to search in TEXTBOXES?")
For Each oleObj In ActiveSheet.OLEObjects
If TypeOf oleObj.Object Is MSForms.TextBox Then
 If oleObj.Object.Value = x Then
  MsgBox "The value you are looking for is in" & oleObj.Name
 End If
End If
Next
End Sub

It will identify what TextBox it is in no matter if it is Text or Number. Just dependends on what you type in the first Msg Box.

 

[cQuinc]

I ran the code provided by Pedie and I ran the code provided by Nalani and both seem to stop on the first "if" statement with a compile error.

"User defined type not defined". I am attempting to run this code in Windows XP mode using Excel 2003.

Quin

 

[Nalani]

I did a Search from this link:

http://www.google.com/advanced_search?q=site:MrExcel.com


MickG, one of the regulars, came up with a bit of code that will probably work for you.

http://www.mrexcel.com/forum/showthread.php?t=347224

Hope that helps.

 

[cQuinc]

Nalani,
Thank you for taking the time to find that code for me. It does everything I had hoped for. I want to repost the code here to make it convienent for anyone else that may have a need for it.

Sub FindTextinTextboxes()
Dim mt, AllDat, Shp As Shape, c
Dim oDat As String
c = 0
On Error Resume Next
 oDat = Application.InputBox(prompt:="Please Enter Text/Value to Find ", Title:="Text Box Find", Type:=2)
For Each Shp In ActiveSheet.Shapes
    If Shp.Type = msoTextBox Then
        With ActiveSheet.Shapes(Shp.Name).TextFrame
            mt = .Characters.Count
 
          If InStr(1, .Characters(1, mt).Text, oDat, vbTextCompare) Then
                AllDat = AllDat & Shp.Name & "  At Position " _
                     & InStr(1, .Characters(1, mt).Text, oDat, vbTextCompare) _
                        & Chr(10)
                           c = c + 1
                   End If
      End With
   End If
Next Shp
If c > 0 Then
    MsgBox "The Value/Text """ & oDat & """ was found in :-" & Chr(10) _
        & AllDat
Else
    MsgBox "No Matches Found"
End If
End Sub

I had to name the sub and put in the end sub statement for it to work. The author MickG did not put that in.

Also a couple of things I noticed. The code does not seem to ever execute the line

Else MsgBox "No Matches Found"

No matter, it is not important.

Also I see that if there are too many matches found the MSG box only displays a portion of them.

I like the fact that it makes a list of all the text boxes that contain the word or number I am searching for, and even tells me how many characters into the text box to look for the word. I found that when the message box is showing the information, the result list can be copied with CTRL-C and pasted elsewhere to maintain a convienent list of the locations.

I will make good use of this code. Thankyou to everyone that made it possible.