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.

Finding Text in Text Boxes

Written by Allen Wyatt (last updated June 17, 2021)
This tip applies to Excel 2007 and 2010


20

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:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Pulling Initial Letters from a String

When working with names or a different series of words, you may need to pull the initial letters from each word in the ...

Discover More

Allowing for Prefixes and Suffixes in Find and Replace

Excel includes a rather simplistic find and replace capability. If you have more complex needs, you'll need to seek out ...

Discover More

Getting Rid of Hard Returns before Endnotes

Are there unexplained (and unwanted) paragraph marks before your endnotes? Here's how to get rid of them so they don't ...

Discover More

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!

More ExcelTips (ribbon)

Placing Textbox Text Into a Worksheet

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 More

Dynamic Text Boxes

You probably know that text boxes can contain text. (Else why call them text boxes?) Did you know that you could make ...

Discover More

Adding a Drop Shadow to a Text Box

One 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 More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 7 - 6?

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

balthamossa2b

@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

balthamossa2b

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

balthamossa2b

@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

balthamossa2b

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!


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.