Retrieve all comments in a workbook

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

Advertisements
This entry was posted in Macros.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s