This Excel tutorial explains how to show all comments and hide all comments in workbook.
You may also want to read:
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
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.
After you run the Macro, all Comment become visible