Replacing in Worksheets and Comments At the Same Time

Written by Allen Wyatt (last updated December 29, 2020)
This tip applies to Excel 2007, 2010, 2013, and 2016


2

Dorothy's students are customizing a checkbook simulation workbook, replacing the default name with their own. For example, she wants them to search for "Jason Anderson" and replace the name with "Sue Smith." There are 22 occurrences of "Jason Anderson" in text boxes throughout eight worksheet tabs, and several more on the worksheets themselves. Dorothy wonders how she can Find and Replace all worksheet occurrences and all comment occurrences at once.

There is no way to do this without using a macro. The macro would need to look through each cell in the workbook, each comment, and each text box to see if changes needed to be made. The following is an example of how this can be done:

Sub ReplaceNames()
    Dim sDefaultName As String
    Dim sNewName As String
    Dim sht As Worksheet
    Dim cmt As Comment
    Dim wks As Worksheet
    Dim sCmt As String
    Dim shp As Shape

    sDefaultName = "Jason Anderson"
    sNewName = InputBox("Enter your first and last name.", _
      "Name Replacement")

    ' Replace cell text
    For Each sht In ActiveWorkbook.Worksheets
        sht.Cells.Replace what:=sDefaultName, _
          Replacement:=sNewName, LookAt:=xlPart, _
          SearchOrder:=xlByRows, MatchCase:=False, _
          SearchFormat:=False, ReplaceFormat:=False
    Next sht

    ' Replace comment text
    For Each wks In ActiveWorkbook.Worksheets
        For Each cmt In wks.Comments
            sCmt = cmt.Text
            If InStr(sCmt, sDefaultName) <> 0 Then
                sCmt = Application.WorksheetFunction. _
                  Substitute(sCmt, sDefaultName, sNewName)
                cmt.Text Text:=sCmt
            End If
        Next cmt
    Next wks
    Set wks = Nothing
    Set cmt = Nothing

    ' Replace text box text
    On Error Resume Next
    For Each wks In ActiveSheet.Parent.Worksheets
        For Each shp In wks.Shapes
            With shp.TextFrame.Characters
                .Text = Application.WorksheetFunction.Substitute( _
                  .Text, sDefaultName, sNewName)
            End With
        Next shp
    Next wks
End Sub

The macro prompts the user to enter a new name and then searches cells, comments, and text boxes to see if there are any occurrences of the default name ("Jason Anderson"). If so, then the appropriate replacement is made.

If you don't want to use macros to do the searching, perhaps a restructuring of the workbook would be helpful. For instance, you could create a "data input worksheet" in which common information—such as the person's name—is entered. In the other worksheets, the name from the data input worksheet is referenced using formulas. Thus, one change in the data input worksheet, and the name is changed in all the other worksheets.

Using this approach can work with comments, as well. You can select the comment or shape and, in the Formula bar, enter a reference formula such as this:

=MyName

This assumes that the user's name, on the data input worksheet, has been associated with the MyName named range. If there needs to be additional information in the comment or shape, then you could construct that text in a cell on the data input worksheet and then assign the contents of that cell to the comment or shape, just as noted for the MyName named range.

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 (2614) applies to Microsoft Excel 2007, 2010, 2013, and 2016.

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

Filtering Columns

The filtering tools provided in Excel make it easy to filter a data list so that only certain rows are displayed. What if ...

Discover More

Showing Print Preview for the Current Page

The Print Preview feature in Excel can be quite helpful. You might think it would be more helpful, though, if it ...

Discover More

Printing Just the Changes and Comments

Word allows you to use its Track Changes feature to add markup to your document. This is great when you have a group of ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Removing Cells Containing Specific Terms

Macros are great for processing large amounts of data quickly. This tip examines several ways you can remove specific ...

Discover More

A Fast Find-Next

Tired of the Find and Replace dialog box blocking the view of your worksheet when you are searching for information? Do ...

Discover More

Getting a List of Matching Cells

The Find and Replace capabilities of Excel allow you to easily locate all the cells in a worksheet that contain specific ...

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 2 + 5?

2020-12-29 11:18:13

Chris

I agree, Kevin. Just tried on Excel 365; the text box reference worked, and when I selected the text box then clicked in the formula bar it showed the name of the text box. With a comment, all attempts to enter the formula suggested ended up either with the result of the formula being in the commented cell, or the formula being shown as text in the comment.


2016-05-28 08:03:12

Kevin

"You can select the comment or shape and, in the Formula bar, enter a reference formula such as this:
=MyName"
I don't think this is possible. Formulas cannot be entered in a comment.


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.