---

Whether you are working on a spreadsheet alone or with a group of colleagues, cell comments are a useful mechanism for flagging items to come back to later, or to ask questions or even just to draw someone else's attention. However, when you have large numbers of comments distributed across multiple sheets it can sometimes be difficult to keep track of things. Sometimes it would be useful to be able to see all the comments in one place — particularly if you have a mixture of comments which are hidden and shown. Luckily we can easily use VBA to write some code to list the cells, values and comments on a separate sheet. As an added bonus we'll add hyperlinks to make it easy to get back to the original cells for editing.

As an example here are two sheets with some comments on them, note that cell D11 on the Data sheet contains a comment which isn't displayed.

To make our example a bit more useful we're going to ignore all comments which aren't tagged with 'Review:' — so we want to ignore the comment in cell B13 on the Data sheet.

The starting place for our VBA code is the Comments collection — this contains the set of Comments for a given range. This has the advantage that we don't have to check each cell to see if it contains a comment — we can check the entire sheet to see if there's a comment on it.

In this code we're going to delete the content of the comments sheet each time the code runs, and we're assuming that a sheet called Comments already exists. The format we want is simple:

The first thing to do then is delete the data already on the comments sheet and set up the header row:

    'Set the header on the comments sheet
    Set wsCmnt = ThisWorkbook.Worksheets("Comments")
    wsCmnt.Cells.Clear
    wsCmnt.Cells(1, 1) = "Comment"
    wsCmnt.Cells(1, 2) = "Value"
    wsCmnt.Cells(1, 3) = "Link"
    cRow = 2 'This is the next blank row on the comments sheet

Now we can start cycling through the worksheets in turn, though we want to exclude the Comments sheet from being processed. To do this we can just use the Worksheets collection:

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> wsCmnt.Name Then

Next we want to grab the Comments collection for the worksheet. This is simple enough to do, we have a reference to the current worksheet so it's just a case of:

    For Each cmnt In ws.Comments

Doing it this was has the advantage that if there are no comments on a worksheet the variable cmnt is set to Nothing and no errors are generated. The Comment object is fairly complex but we're really only interested in two things, the text of the comment and details of the cell that the comment is linked to. In the first place we can just grab the Text property, and in the second we want the Parent object. The Text is simple enough, and in our case we want to only include comments that start with 'Review:'

                cTxt = cmnt.Text
                If Left(cTxt, 7) = "Review:" Then
                    wsComments.Cells(cRow, 1) = Right(cTxt, Len(cTxt) - 7)

For the value in the cell containing the comment we can use the Parent.Value property:

                    wsComments.Cells(cRow, 2) = cmnt.Parent.Value

Getting the address for the hyperlink is a bit trickier in that the Address property only gives us the cell address, it doesn't include the worksheet name. So we can simply append that to get the full address and then we put that into a hyperlink on the sheet:

                    wsComments.Cells(cRow, 3).Hyperlinks.Add Anchor:=wsComments.Cells(cRow, 3), _
                        Address:="", SubAddress:=ws.Name & "!" & cmnt.Parent.Address

And that's pretty much it. The end result, for this example, looks like this:

Putting this all together gives us the complete code:

Sub CreateCommentSheet()
Dim ws As Worksheet
Dim wsCmnt As Worksheet
Dim cmnt As Comment
Dim cRow As Integer
Dim cTxt As String

    'Set the header on the comments sheet
    Set wsCmnt = ThisWorkbook.Worksheets("Comments")
    wsCmnt.Cells.Clear
    wsCmnt.Cells(1, 1) = "Comment"
    wsCmnt.Cells(1, 2) = "Value"
    wsCmnt.Cells(1, 3) = "Link"
    cRow = 2 'This is the next blank row on the comments sheet

    For Each ws In ThisWorkbook.Worksheets
        If ws.Name <> wsCmnt.Name Then
            For Each cmnt In ws.Comments
                cTxt = cmnt.Text
                If Left(cTxt, 7) = "Review:" Then
                    wsCmnt.Cells(cRow, 1) = Right(cTxt, Len(cTxt) - 7)
                    wsCmnt.Cells(cRow, 2) = cmnt.Parent.Value
                    wsCmnt.Cells(cRow, 3).Hyperlinks.Add Anchor:=wsCmnt.Cells(cRow, 3), _
                        Address:="", SubAddress:=ws.Name & "!" & cmnt.Parent.Address
                    cRow = cRow + 1
                End If
            Next
        End If
    Next

End Sub

The code can be tweaked and improved easily enough. For example rather than select just the 'Review:' comments you could just grab all of them. The comment object includes properties for Author or whether the comment was Visible or not.

Return to home page