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

by Allen Wyatt
(last updated September 7, 2015)


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
            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
    MsgBox "No more found"
    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, _
                .UnderlineStyle = msoUnderlineHeavyLine
                .Bold = True
            End With
        End If
    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
End Sub

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. ...


Making Bookmarks Bold

Do you want an easy way to see all the bookmarks in your document? Word provides a way to make them visible, or you can use ...

Discover More

Keeping a Picture Title with the Picture

Pictures and their titles go together like peanut butter and jelly. (Wow, did I just say that?) Seriously, pictures and ...

Discover More

Printing Multiple Selections

Need to print several portions of a worksheet all on a single piece of paper? Here's an easy way you can get what you need ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!


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 that ...

Discover More

Resizing a Text Box in a Macro

Text boxes are easy to add to a document and manually resize, as needed. If you want to resize the text box in a macro, ...

Discover More

Using Text Boxes

Text boxes are handy for placing information in a container that can "float" over your worksheet. This tip explains what text ...

Discover More

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 for this tip:

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}] in your comment text. You’ll be prompted to upload your image when you submit the comment. 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 + 8?

2016-06-29 00:55:39


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



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


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?

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??

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


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

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


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


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!

Newest Tips

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.

Links and Sharing