Excel show all comments in worksheet

This Excel tutorial explains how to show all comments and hide all comments in workbook.

You may also want to read:

Excel consolidate all Comments in the workbook

Excel delete all comments in Workbook

Excel show comments in worksheet

Comment is a yellow dialog box that you can write comment regarding specific Cell.

To insert a Comment, right click on a Cell and then select Insert Comment

excel_show_comments_04

When we insert a Comment into a Cell, there would be a red triangle on the top right of the Cell.

Comment is hidden by default, you need to hover the mouse over the Cell in order to display the Comment.

excel_show_comments_01

We can also show the Comment all the time. Right click on the Cell, then select Show/Hide Comments

excel_show_comments

Suppose you have many hidden comments spreading over different Cells, it is extremely difficult to locate all those comments by looking for the red triangle, especially when there are colored Cells. Even if we can locate them with eye ball, it is time consuming to hide or show all comments manually Cell by Cell.

Excel VBA show all comments in worksheet

In this section, I am going to write a Macro to show all comments in the workbook, and to facilitate testing, I have another Macro to hide all comments.

The below code is extremely short, basically I loop through all worksheets in Activeworkbook and then look for the Cells contain Comments.

Sub showComment()
 On Error Resume Next
    For Each ws In ActiveWorkbook.Sheets
        Set allCommentRng = ws.Cells.SpecialCells(xlCellTypeComments)
        For Each Rng In allCommentRng
            Rng.Comment.Visible = True
        Next
    Next
On Error GoTo 0
End Sub
Sub hideComment()
 On Error Resume Next
    For Each ws In ActiveWorkbook.Sheets
        Set allCommentRng = ws.Cells.SpecialCells(xlCellTypeComments)
        For Each Rng In allCommentRng
            Rng.Comment.Visible = False
        Next
    Next
On Error GoTo 0
End Sub

Example –  show all comments in worksheet

In the below example, we have Sheet1 and Sheet2 with hidden Comment.

excel_show_comments_03

After you run the Macro, all Comment become visible

excel_show_comments_02

 

Outbound References

http://www.excelforum.com/excel-formulas-and-functions/493625-macro-delete-comments-what-if-theres-none.html

 

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *