Finding Text in Text Boxes

[UPDATED CODE]

Sub FindString()

Dim startIndex As Range
Dim tBox As TextBox
Dim idToFind As String
Dim sTemp As String
Dim notFound As Boolean

idToFind = InputBox("Enter the string:")

If Trim(idToFind) = "" Then
MsgBox "Nothing entered; goodbye."
Exit Sub
End If

Set startIndex = ActiveCell
notFound = True

For Each tBox In ActiveSheet.TextBoxes

sTemp = tBox.Text

If InStr(LCase(sTemp), LCase(idToFind)) <> 0 Then
tBox.Select

With tBox.Characters(Start:=iPos, _
Length:=Len(idToFind)).Font
.ColorIndex = 3
.Bold = True
End With

notFound = False
End If

Next

If notFound Then
MsgBox "No matches found."
Exit Sub
End If

MsgBox "Done."
startIndex.Select
Set startIndex = Nothing

End Sub




It should work across the latest version of Excel as of 4/20/2021. :)

The 2nd macro does not loop thru and find all instances in each textbox (same problem as DD report 6/29/16)

This works for me only when the only objects I have at a particular worksheet are exclusively textboxes.When I tried to run at a worksheet with charts also present, a runtime error gets thrown regarding "out of range", which makes sense as the code relies upon .TextRange. As noted at the explanation, "If you prefer to limit your search to only text boxes, you can step through the TextBoxes collection instead of the Shapes collection"... could you show what the code would look like adjusted for the textboxes collection?

How would i modify this macro to search all text boxes in the entire Workbook (all active sheets), not just the current sheet? Thanks.

So far when I use the 2nd macro, it only finds the first instance. It does not loop around and find additional instances. Is it possible to find all instances of text in a textbox.

Thank you in advance

@Cameron

1. Alt+F11 to open macro interface.
2. Insert > New Module
3. On the right navigation bar a Module1 has appeared, double click on it.
4. Paste FindInShape1 code.
5. Click on any line of the pasted code so that the test cursor appears in said line.
6. F5 to execute.
7. ???
8. Profit.

I am needing to find text boxes and delete them. I am new to making macros so I am struggling. Can anyone give me any assistance?
Thanks

I am using the first macro and it throws me the error reading

'This member can only be accessed for a single shape'

I tried using this statement
sTemp = shp.TextEffect.Text
instead of
sTemp = shp.TextFrame2.TextRange.Characters.Text

can you please help??
Thanks.

@Winner 2

Simply loop through through all Worksheets with something like

For Each wsh1 In ActiveWorkbook

How could I adapt this to seach text boxes on all pages in a workbook? Thanks

balthamossa2b

You're right str as variant is overkill I don't know what I was thinking about but thanks for your comment.

Chiara
Try this, I have not tested it but it makes the textbox stay open so you can find it (assuming that all the text boxes are only visible when you place the mouse cursor above them.

If InStr(1, Tb.Characters.Text, sFind) > 0 Then
tb.Visible = True

Incidentally: very bad things may happen if your textbox contains non-ASCII characters.

It took me three weeks and multiple visits to Chip Pearson's blog to solve that issue in the past. Basically it's pretty tough to correctly detect the character encoding you are using and to pass it as a variable.

@Kevin I would think so, yes.

The most elegant solution is to use a chunk of Peter's code down there and to loop through the sheet.Textboxes collection instead of the Shapes one.

Which before reading his macro I didn't even know it was a thing.


(though his use of str As Variant seems a it overkill, but I haven't really tested his macro to say if you can just use a regular String)

Is bathamossa2b indicating my error received of "The specified value is out of range" received at line sTemp = shp.TextFrame2.TextRange.Characters.Text ?
If so I would like the details of what to replace with what, or other help.
Anticipating some help - Thanks

FYI: this won't work in things that look like textboxes but aren't (say, captions in SmartArt or Ellipse). Plus if there are non-Textbox Shapes in your sheet this macro will give you an error for trying to search their TextFrame when there's none.

An easy way to bypass this (which I've done before but I don't have the code at hand) is to check the shape type at the beginning: if it's a Textbox go for its Textframe; else go for its Caption.

Hi all,
I would need something similar to macro FindInShape1.
In particular, after finding th e textbox containing the searched text I need the view (the cursor) to move above it, otherwise I cannot find the shape anyway.

Do you think i there is a solution?

Thanks for your help!

The original query mentioned Find & Replace, so here is a method for that.

Sub EditTxtBox02()
Dim str As Variant
Dim Tb As Object
Dim wks1 As Worksheet
Dim sFind As String, sReplace As String

sFind = InputBox("Find What?")
sReplace = InputBox("Replace with?")

If Len(sFind) = 0 Then Exit Sub

Set wks1 = ActiveSheet

For Each Tb In wks1.TextBoxes
If InStr(1, Tb.Characters.Text, sFind) > 0 Then
str = Replace(Tb.Characters.Text, sFind, sReplace)
Tb.Characters.Text = str
Tb.AutoSize = True
End If
Next Tb
End Sub

Thanks for this macro. I needed a way to find and delete a text box. I used FindInShape1, modified to find and delete a specific text box without manual intervention. It worked great. The basic issue here (for me) is code to find a text box containing specific text.

Thanks again.

I see I missed "Text Boxes". Close but no cigar!

Excel 2010 (can't prove with prior versions - If you use Filters, you can search a column for text. Simply click the filter arrow and key the search text into the Search box. All rows containing that text (even if it's in the middle of the text string)are returned in the filter. This is not as easy if you want to search in mulitple columns, but works great for one column.
I'm adding the macro to do the multiple column searches! Thanks!