List comments in Excel

Question

How to list ALL Comments on Worksheet on one tab??

I know this is possible, but I cannot figure out how to do it. I would like to print a page of all comments contained on worksheet for ease of use. Any ideas??

Answer 1

I already had a function that almost did what you're asking, so just tweaked a little. This macro (listComments2) should do the trick.

Sub listComments2()
'Thanks to http://superuser.com/a/809212/529100
Dim commentWS As Worksheet

Application.ScreenUpdating = False

Set commentWS = ActiveWorkbook.Worksheets.Add(after:=ActiveWorkbook.Worksheets(Sheets.Count))
commentWS.Name = "Comments"

Dim cmts As New Collection

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "Comments" Then
        For Each cmt In ws.Comments
            cmts.Add cmt.Text
        Next cmt
    End If
Next ws

Dim commentArray() As Variant
commentArray = toArray(cmts)

Dim i As Long
For i = LBound(commentArray) To UBound(commentArray)
    With commentWS
        .Cells(i, 1).Value = commentArray(i)
    End With
Next i

' Reformat columns/worksheet
With commentWS.Cells
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    .EntireColumn.AutoFit
End With

Application.ScreenUpdating = True

End Sub


Function toArray(cmt As Collection)

Dim i As Long
Dim arr() As Variant
ReDim arr(1 To cmt.Count) As Variant
For i = 1 To cmt.Count
    arr(i) = cmt(i)
Next i

toArray = arr
End Function