Please Note: This article is written for users of the following Microsoft Excel versions: 2007 and 2010. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Finding Text in Text Boxes.
Walter has a worksheet that has a number of text boxes in it. He would like to search through those text boxes to find some specific text, but Find and Replace doesn't seem capable of finding text in text boxes. He wonders if there is a way to search through text boxes.
Walter is right; you cannot find text located in text boxes in Excel. To test this, we opened a brand new workbook, placed a single phrase in it ("my message"), and then placed some random text and numbers in other cells in the worksheet. Then, with the text box not selected, Ctrl+F was pressed to search for "my message." Excel dutifully reported that it couldn't find the text, even though it was still right there, in the text box.
Fortunately, you can search for text in a text box using a macro. Each text box in a worksheet belongs to the Shapes collection, so all you need to do is step through each member of the collection and see if it contains the desired text. Here's a macro that prompts for a search string and then looks for it in the text boxes.
Sub FindInShape1() Dim rStart As Range Dim shp As Shape Dim sFind As String Dim sTemp As String Dim Response sFind = InputBox("Search for?") If Trim(sFind) = "" Then MsgBox "Nothing entered" Exit Sub End If Set rStart = ActiveCell For Each shp In ActiveSheet.Shapes sTemp = shp.TextFrame2.TextRange.Characters.Text If InStr(LCase(sTemp), LCase(sFind)) <> 0 Then shp.Select Response = MsgBox( _ prompt:=shp.Name & vbCrLf & _ sTemp & vbCrLf & vbCrLf & _ "Do you want to continue?", _ Buttons:=vbYesNo, Title:="Continue?") If Response <> vbYes Then Set rStart = Nothing Exit Sub End If End If Next MsgBox "No more found" rStart.Select Set rStart = Nothing End Sub
This macro looks through all the shapes in the worksheet, not just the text boxes. If you prefer to limit your search to only text boxes, you can step through the TextBoxes collection instead of the Shapes collection; either way will work fine.
Notice, as well, that this approach stops each time it finds matching text (the case of the text doesn't matter) and asks you if you want to continue. You may, instead, want a macro that simply marks the matching text in text boxes. This can be done with a shorter macro, as shown here:
Sub FindInShape2() Dim shp As Shape Dim sFind As String Dim sTemp As String Dim iPos As Integer Dim Response sFind = InputBox("Search for?") If Trim(sFind) = "" Then MsgBox "Nothing entered" Exit Sub End If sFind = LCase(sFind) For Each shp In ActiveSheet.Shapes sTemp = LCase(shp.TextFrame2.TextRange.Characters.Text) iPos = InStr(sTemp, sFind) If iPos > 0 Then With shp.TextFrame2.TextRange.Characters(Start:=iPos, _ Length:=Len(sFind)).Font .UnderlineStyle = msoUnderlineHeavyLine .Bold = True End With End If Next MsgBox "Finished" End Sub
This macro underlines the located text using a heavy line, and then makes it bold. When you are done, you probably want to change the text back to regular text. You can do so by using the following macro:
Sub ResetFont() Dim shp As Shape For Each shp In ActiveSheet.Shapes With shp.TextFrame2.TextRange.Characters.Font .UnderlineStyle = msoNoUnderline .Bold = False End With Next End Sub
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (11282) applies to Microsoft Excel 2007 and 2010. You can find a version of this tip for the older menu interface of Excel here: Finding Text in Text Boxes.
Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!
Want to get rid of your text boxes and move their text into the worksheet? It's going to take a macro-based approach, ...
Discover MoreYou probably know that text boxes can contain text. (Else why call them text boxes?) Did you know that you could make ...
Discover MoreOne way to make your text boxes "stand off" the page is to add a drop shadow to them. This tip shows just how easy it is ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2021-04-20 19:17:50
JG
[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. :)
2018-12-04 13:48:37
Ker9
The 2nd macro does not loop thru and find all instances in each textbox (same problem as DD report 6/29/16)
2017-10-12 11:02:26
JT
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?
2017-04-27 07:22:11
Eric
How would i modify this macro to search all text boxes in the entire Workbook (all active sheets), not just the current sheet? Thanks.
2016-06-29 00:55:39
DD
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
2016-01-22 05:07:36
balthamossa2b
@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.
2016-01-21 10:53:04
Cameron
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
2015-08-10 09:16:10
Rashmi R
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.
2015-07-01 07:44:45
@Winner 2
Simply loop through through all Worksheets with something like
For Each wsh1 In ActiveWorkbook
2015-06-30 10:18:27
Winner 2
How could I adapt this to seach text boxes on all pages in a workbook? Thanks
2014-10-24 17:34:11
Peter Atherton
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
2014-10-21 05:27:48
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.
2014-10-21 05:11:08
@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)
2014-10-20 19:54:08
Kevin
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
2014-10-20 07:53:56
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.
2013-09-09 06:37:30
Chiara
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!
2012-02-15 10:59:07
Peter Atherton
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
2012-02-13 12:41:58
William Simpson
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.
2012-02-13 09:35:57
Charlie Ruffin
I see I missed "Text Boxes". Close but no cigar!
2012-02-13 08:52:55
Charlie Ruffin
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!
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2024 Sharon Parq Associates, Inc.
Comments