Excel show all comments in worksheet

This Excel tutorial explains how to show all comments and hide 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

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.

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

Assume that 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 GoTo errHandler
    For Each ws In ActiveWorkbook.Sheets
        Set allCommentRng = ws.Cells.SpecialCells(xlCellTypeComments)
        For Each Rng In allCommentRng
            Rng.Comment.Visible = True
        Next
    Next
errHandler: Exit Sub
End Sub

Sub hideComment()
 On Error GoTo errHandler
    For Each ws In ActiveWorkbook.Sheets
        Set allCommentRng = ws.Cells.SpecialCells(xlCellTypeComments)
        For Each Rng In allCommentRng
            Rng.Comment.Visible = False
        Next
    Next
errHandler: Exit Sub
End Sub

Example –  show all comments in worksheet

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

After you run the Macro, all Comment become visible

 

Outbound References

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

 

 

Excel show all comments in worksheet