cfo.com

Print this article | Return to Article | Return to CFO.com

How to Search for Cell Comments Using Excel

Here's a handy Excel macro that will search and find cell comments quickly.
Bill Jelen, CFO.com | US
February 1, 2012

David H. asks,"Using the 'Find' feature in Excel works when you need to locate items quickly, but is there a way to use this, or another function, that will expand the search to include text in any comments within the worksheet?"

Cell comments are a great way to document a spreadsheet. But if you later need to search all cell comments that include particular text, it is fairly difficult.

One option is to use the Show All Comments icon on the Review tab in Excel. This will display all of the comments. If you have a small data set, then it is easy to read through the comments, looking for certain text. However, this solution is not perfect. If the text of the comment extends beyond the size of the comment, you will not notice text that extends beyond the comment.

Fig. 1

2012_02_01_MrExcel Fig 1

 

The next option is a macro to search through comments. Follow these steps to add a macro to the workbook:

1. Type Alt+T followed by M, S to check your macro security. If the setting is currently "Disable all macros without notification", change it to "Disable all macros with notification".

2. Use File, Save As to save your file as either an XLSM or XLSB or XLS file type. The default XLSX file precludes macros.

3. Use Alt+F11 to switch to VBA

4. Select Insert, Module to insert a new module into your workbook

5. Copy and paste the following into the blank module.

Sub SearchCommentsOnCurrentWorksheet()

    Dim c As Comment

    Dim SearchFor As Variant

    Dim TxtToSearch As Variant

    Dim Ctr As Integer

    Dim Msg As Variant

    SearchFor = LCase(InputBox(Prompt:="Text to find in comments?"))

    Application.DisplayCommentIndicator = xlCommentIndicatorOnly

    Ctr = 0

    For Each c In ActiveSheet.Comments

        TxtToSearch = LCase(c.Text)

        If InStr(1, TxtToSearch, SearchFor) > 0 Then

            c.Visible = True

            c.Parent.Select

            Ctr = Ctr + 1

            If Ctr = 1 Then

                Msg = " comments found:" & vbLf & c.Parent.Address

            Else

                Msg = Msg & vbLf & c.Parent.Address

            End If

        End If

    Next c

    If Ctr = 0 Then

        MsgBox x & " not found in current worksheet comments."

    Else

        MsgBox Ctr & Msg

    End If

End Sub

Then continue with these next three steps:

1. Press Alt+Q to close VBA and return to Excel.

2. Press Alt+F8 to display a list of macros.

3. Select SearchCommentsOnCurrentWorksheet and press Run

The macro will ask you for the text that you want to find.

Fig. 2

2012_02_01_MrExcel Fig 2

 

Enter some text. Click OK. The macro will find all comments that contain the text. The comment will be shown for those cells and a list of the cells will appear in the dialog box, as shown in Figure 3.

Fig. 3

2012_02_01_MrExcel Fig 3

 

To subsequently hide the comments, click the Show All Comments icon in the Review tab of the ribbon. Click this icon twice. The first click will show all comments. The second click will hide all comments.

You can also use this macro to find all comments created by one particular person. Here are all of the Joe Smith comments (see Figure 4).

Fig. 4

2012_02_01_MrExcel Fig 4

 

Caution: if you regularly keep some comments displayed to provide instructions, you should not use this macro, as it will hide all comments.

If you find that this macro is useful and you will want to have it always available, you can move the code to your personal macro workbook and assign the macro to an icon on the Quick Access Toolbar.


CFO Contributor and Microsoft MVP Bill Jelen is the author of 34 books about Microsoft Excel, including Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. Bill regularly selects reader questions from the Community Center (to the right of this article) to answer in his columns.  

 




CFO Publishing Corporation 2009. All rights reserved.