Print a Customized List of Excel Comments

If you’ve added comments to an Excel worksheet, you have a couple of built-in options for printing the comments.

  • Show the comments on the worksheet, and print them as displayed.
  • Print the list of comments at the end of the worksheet, on a separate printed page.

Printing the comments on the worksheet is okay if there are only a couple of comments, and you can arrange them so they don’t cover the data.

CommentsPreview

For more than a couple of comments, the list at the end of the worksheet is a better choice. However, with the built-in list printing option, you just get the cell address and comment, printed in a long, single column.

CommentsPreviewEnd

Create Your Own List of Comments

Instead of using the built-in list of printed comments, you can use a macro to create your own list of comments on a separate worksheet, and print that list. It’s also a great way to review all the comments on a worksheet, and use sorting or filtering to focus on specific comments.

CommentPrintList02

Shown below is the Excel VBA code to create a list of comments from the active sheet, written by Dave Peterson. For more comment programming examples, including Dave’s code to list all the comments in the entire workbook, see Excel Comments VBA.

The Comment List Code

The ShowComments macro adds a new sheet to the workbook, and lists all the comments, the comment author name, and the comment cell’s value, address and name (if any). At the end of the macro, the first row is formatted in bold font, and the column widths are autofit.

Sub ShowComments()
'posted by Dave Peterson
    Application.ScreenUpdating = False
Dim commrange As Range
Dim mycell As Range
Dim curwks As Worksheet
Dim newwks As Worksheet
Dim i As Long

Set curwks = ActiveSheet

On Error Resume Next
Set commrange = curwks.Cells _
.SpecialCells(xlCellTypeComments)
On Error GoTo 0

If commrange Is Nothing Then
MsgBox "no comments found"
Exit Sub
End If

Set newwks = Worksheets.Add

newwks.Range("A1:E1").Value = _
Array("Address", "Name", "Value", "Author", "Comment")

i = 1
For Each mycell In commrange
With newwks
i = i + 1
On Error Resume Next
.Cells(i, 1).Value = mycell.Address
.Cells(i, 2).Value = mycell.Name.Name
.Cells(i, 3).Value = mycell.Value
.Cells(i, 4).Value = mycell.Comment.Author
.Cells(i, 5).Value = mycell.Comment.Text
End With
Next mycell

With newwks
.Rows(1).Font.Bold = True
.Cells.EntireColumn.AutoFit
End With

Application.ScreenUpdating = True

End Sub

_____________

You may also like...

8 Responses

  1. Rick Rothstein (MVP - Excel) says:

    Here is a different macro (using about 1/3 less active code lines) to do the same thing…

    Sub ListComments()
    Dim X As Long, RngName As String, newwks As Worksheet, curwks As Worksheet
    Application.ScreenUpdating = True
    Set curwks = ActiveSheet
    Set newwks = Worksheets.Add
    If curwks.Comments.Count Then
    newwks.Range(“A1:E1”).Value = Array(“Address”, “Name”, “Value”, “Author”, “Comment”)
    On Error Resume Next
    For X = 1 To curwks.Comments.Count
    RngName = “”
    With curwks.Comments(X)
    RngName = .Parent.Name.Name
    Err.Clear
    Range(“A1”).Offset(X).Resize(1, 5) = Array(.Parent.Address, RngName, .Parent.Value, .Author, .Text)
    End With
    Next
    Else
    MsgBox “No comments found!”, vbCritical
    End If
    Application.ScreenUpdating = True
    End Sub

  2. ramesh says:

    thanks for the code, btwthere is also a nice addins workbook navigation to try with list of cooments having hyperlink of comments with images from below site.

    http://shahshaileshs.web.officelive.com/Addins.aspx

  3. Rick Rothstein (MVP - Excel) says:

    I just noticed an “error” (which does not affect the final outcome of my code, but it will affect how quickly it runs)… the first Application.ScreenUpdating statement in my code is set to True… it should be set to False (the setting to True at the end of the code is correct as is). Here is the corrected code for those who might want to copy/paste it into their own programs…

    Sub ListComments()
    Dim X As Long, RngName As String, newwks As Worksheet, curwks As Worksheet
    Application.ScreenUpdating = False
    Set curwks = ActiveSheet
    Set newwks = Worksheets.Add
    If curwks.Comments.Count Then
    newwks.Range(“A1:E1?).Value = Array(“Address”, “Name”, “Value”, “Author”, “Comment”)
    On Error Resume Next
    For X = 1 To curwks.Comments.Count
    RngName = “”
    With curwks.Comments(X)
    RngName = .Parent.Name.Name
    Err.Clear
    Range(“A1?).Offset(X).Resize(1, 5) = Array(.Parent.Address, RngName, .Parent.Value, .Author, .Text)
    End With
    Next
    Else
    MsgBox “No comments found!”, vbCritical
    End If
    Application.ScreenUpdating = True
    End Sub

  4. Contextures Blog » Change Name in Excel Comments says:

    […] you’re not sure what comment names are in the workbook, you can create a list of Excel comments, with their details. Then, run this Change Comment Name code – more than once, if there are […]

  5. آپلود عکس says:

    Hi Debra, Excellent article! We are linking to this great article on our website.
    Thanks a lot

  6. چیلر says:

    Hi Debra, nice.
    tnx

  7. Jason says:

    Is there a way to utilize showcomments VBA by Dave Peterson to pull just part of the comment over to the new worksheet? I have coding in place (snippet below) to create a cell comment. I want to leave the cell comment in place; however, would like to use Dave’s coding to copy everything including & after “Staff 90 day Plan” to the new worksheet.

    cmtText = Format(Now, “mm/dd/yy hh:mm:ss ampm”) & “-” & Application.UserName & “-” & staffText & ” ” & goalText & ” ” & vbCrLf & “STAFF 90 day Plan:” & ” ” & cmtText & Chr(10).

    Thanks

Leave a Reply to چیلر Cancel reply

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