List all comments with sheet/cell references

[excel_stuff]

Hi,


I have made several comments in a large Excel file and want to list them all in one sheet with sheet and cell references. Is there a way to do this?


Many thanks

 

[Luke M]

I recommend trying the method shown here:

http://blog.contextures.com/archives/2012/08/16/print-numbered-list-of-excel-comments/


Or here, if you want more customization:

http://blog.contextures.com/archives/2010/08/27/print-a-customized-list-of-excel-comments/

 

[excel_stuff]

Thanks, Luke. It looks like the list comment macro in the second link you mentioned should do the trick, although its a shame that there is no built-in function to list all comments at the end of the sheet or in a new sheet.


thanks for your help.

 

[Luke M]

Agreed. The only built-in function is to show them when you print. Although maybe the idea was so as not to confuse people...I'm sure someone would start to think that they can type in the comments worksheet and somehow that would change the embedded comments in the cell. =P

 

[excel_stuff]

Actually that would be a neat thing to be able to do in Excel, i.e. list all comments in one sheet and you could change them on that sheet and it would update in relevant sheets/cells, although I am not sure about the utility of such function.


I remain convinced that one should be able to get a list of all comments with references in one sheet.

 

[SirJB7]

Hi, excel_stuff!


This code will do the job. You just need 2 things, first create a sheet for comments summary, and second adjust its name in constant ksWSComments.


-----

[pre]

Option Explicit

Sub CommentsSummary()
' constants
Const ksWSComments = "Comments"
' declarations
Dim wsC As Worksheet
Dim I As Long, J As Long, A As String, C As Range
' start
Set wsC = Worksheets(ksWSComments)
With wsC
Range(.Rows(2), .Rows(.Rows.Count)).ClearContents
End With
' process
With wsC
J = 1
For I = 1 To ThisWorkbook.Worksheets.Count
If ThisWorkbook.Worksheets(I).Name <> ksWSComments Then
For Each C In ThisWorkbook.Worksheets(I).UsedRange
If Not C.Comment Is Nothing Then
J = J + 1
.Cells(J, 1).Value = C.Parent.Name
.Cells(J, 2).Value = C.Address(False, False)
.Cells(J, 3).Value = C.Comment.Author
.Cells(J, 4).Value = C.Comment.Text
End If
Next C
End If
Next I
End With
' end
With wsC
.Activate
.Range("A1").Select
.Range("A2").Select
End With
Set wsC = Nothing
Beep
End Sub

[/pre]
-----


Just advise if any issue.


Regards!


EDITED


Link:

https://dl.dropboxusercontent.com/u/60558749/List%20all%20comments%20with%20sheet_cell%20references%20%28for%20excel_stuff%20at%20chandoo.org%29.xlsm