Replacing in Worksheets and Comments At the Same Time

by Allen Wyatt
(last updated May 28, 2016)

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.

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

Printing Reports

The Report Manager allows you to create specialized reports that can be easily printed from your worksheet data. This tip ...

Discover More

Hiding the Taskbar when It is Not in Use

Don't like the Taskbar visible on the screen? You can easily hide it when you aren't using it by making just one small ...

Discover More

Opening a Backup File

If you have Word configured to save backup copies of your document, you may want to actually load one of those copies at ...

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)

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 All Instances of a Value

Searching for information in an Excel worksheet generally goes very smoothly. There can be times, however, when the ...

Discover More

Inconsistent Behavior of Find and Replace Dialog Box

The Find and Replace feature in Excel is one of the workhorse editing tools you can use. When the Find and Replace dialog ...

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

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.