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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,383
Messages
5,528,377
Members
409,817
Latest member
JiNXX9500

This Week's Hot Topics

  • Change military grades into rank
    Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
  • VBA COUNTIF SOLUTION
    Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
  • INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
    Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top