Replacing in Worksheets and Comments At the Same Time

by Allen Wyatt
(last updated January 20, 2020)

1

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

Keeping Table Rows Together

When you create a table that extends beyond a single page, you may want to make sure that the information in a table row ...

Discover More

Jumping to a Section

One way you can navigate through a document is to jump from section to section. Here's the traditional way to quickly get ...

Discover More

Last Non-Zero Value in a Row

If you have a lot of values in a single row, you might want to pull the last non-zero value from that row. There are a ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling 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

Finding Based on Displayed Results

Want to use Excel's Find feature to locate cells based on what those cells display? It's easy if you know how to adjust ...

Discover More

Finding Boolean Values

Excel worksheets can contain all sorts of data. One thing you might store in a worksheet is a range of Boolean (TRUE or ...

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

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.