How can I search for text within text boxes?

cQuinc

New Member
Joined
Mar 19, 2011
Messages
11
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
Hi Quin, welcome to forum.:)

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

Code:
[/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

New Member
Joined
Mar 19, 2011
Messages
11
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

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
It basically looks for x you will input in inputbox without ""
If it contains then you can enter your statement in

Code:
[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...
 
Last edited:

Nalani

Well-known Member
Joined
Apr 10, 2009
Messages
1,047

ADVERTISEMENT

How about this little modification to pedie's code:
Rich (BB 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

New Member
Joined
Mar 19, 2011
Messages
11
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
 

cQuinc

New Member
Joined
Mar 19, 2011
Messages
11
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.

Code:
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
Code:
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,302
Messages
5,836,512
Members
430,436
Latest member
fefenouil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top