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)

17

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

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

Increasing Font Size In Worksheet Tabs

While Excel does not have an intrinsic way to change the font in on a worksheet tab, Windows does.

Discover More

ISO Week Numbers in Excel

Work in an industry that uses ISO standards when it comes to working with dates? You'll love the formula in this tip which ...

Discover More

Nudging a Table

When laying out a page, you often need to move objects around to get them into just the right position. Word allows you to ...

Discover More

Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies 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

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

Sizing Text Boxes and Cells the Same

Adding a text box to a worksheet is easy. Making sure that text box is the exact size of a cell in the worksheet may not be ...

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}] 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 6 + 4?

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.