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

Adjusting Chart Size

If you don't have Excel installed on your system, Microsoft Graph is a handy way to create simple charts for your ...

Discover More

Changing Label Printing Order

If you want to change the order in which labels are printed when doing a mail merge, Word doesn't provide many options. ...

Discover More

Appending to a Non-Document Text File

Your macros can easily add information to the end of an existing text file. This is done by opening the target file in ...

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)

Replacing Only Whole Words in Excel

Excel's Find and Replace capabilities are handy, but they aren't as full-featured as those in Word. One shortcoming is ...

Discover More

Making All Occurrences Bold

Want to make instances of a given word or phrase bold throughout a worksheet? Here's a way you can make the change quickly.

Discover More

Allowing for Prefixes and Suffixes in Find and Replace

Excel includes a rather simplistic find and replace capability. If you have more complex needs, you'll need to seek out ...

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