Hello all,
You want to retrieve all the comments from a workbook in a neat way, without having to print the workbook (you know you can print the comments in a separate page, right?). The following sub will pull all comments and put them in a new worksheet, neatly, with the sheet title, hyperlinked cell reference, the cell value and the cell comment.
Try it, you might like it.
Sub RetrieveCasdfomments()
‘get all comments from a workbook and put them in a new worksheet
Dim rgCmt As Range, rgComments As Range, lRowLoop As Long, shtLoop As Worksheet, shtComments As Worksheet
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets.Add before:=Sheets(1)
Set shtComments = ActiveSheet
With shtComments
‘create and format the comment summary sheet
shtComments.Name = “Comments”
With .Columns(“A:D”)
.VerticalAlignment = xlTop
.WrapText = True
End With
.Columns(“C”).ColumnWidth = 15
.Columns(“D”).ColumnWidth = 60
.PageSetup.PrintGridlines = True
.[a1] = “Sheet”
.[b1] = “Cell”
.[c1] = “Value”
.[d1] = “Comment”
.Rows(1).Font.Bold = True
.Tab.Color = 255
.Tab.TintAndShade = 0
End With
lRowLoop = 2
For Each shtLoop In ActiveWorkbook.Worksheets
‘loop through all worksheets and retrieve the comments
If shtLoop.Name <> shtComments.Name And shtLoop.Comments.Count > 0 Then
On Error Resume Next
Set rgComments = shtLoop.Cells.SpecialCells(xlCellTypeComments)
If Err = 0 Then
For Each rgCmt In rgComments.Cells
If Trim(rgCmt.Comment.Text) <> “” Then
shtComments.Cells(lRowLoop, 1) = shtLoop.Name
shtComments.Hyperlinks.Add Anchor:=shtComments.Cells(lRowLoop, 2), Address:=””, _
SubAddress:=”‘” & shtLoop.Name & “‘!” & rgCmt.Address(0, 0), TextToDisplay:=rgCmt.Address(0, 0)
shtComments.Cells(lRowLoop, 3) = “‘” & rgCmt.Text
shtComments.Cells(lRowLoop, 4) = “‘” & rgCmt.Comment.Text
lRowLoop = lRowLoop + 1
End If
Next rgCmt
Else
Err.Clear
End If
End If
Next shtLoop
shtComments.Activate
‘clean up
If Application.WorksheetFunction.CountA(shtComments.Columns(1)) = 1 Then
MsgBox “No comments in workbook”
Application.DisplayAlerts = False
shtComments.Delete
Application.DisplayAlerts = True
End If
Application.Calculation = xlCalculationAutomatic ‘xl95 uses xlAutomatic
Application.ScreenUpdating = True
End Sub
Thomas