List all comments in a workbook, by sheet and cell

[L Mehl]

Hello --

I had success with the code posted by

Patrick Molloy
Subject: Re: Search comment with value
Date: 2002-08-01 11:56:07 PST

to list the comments in a sheet.

Does anyone know of or can you suggest how to embed this code in a loop
through all sheets in the workbook, so I can document the workbook with one
run of a process, rather than having to run the process for each sheet?

I want the new sheet to have the following columns:
Workbook name
Worksheet name
Cell reference
Comment

Thanks for any help.

Larry Mehl


Patrick's code:
Sub FindComments()
Dim cell As Range, rng As Range
Dim ws As Worksheet, rw As Long
On Error Resume Next
Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)
If rng Is Nothing Then
MsgBox "No cells with comments were found."
Else
Set ws = Worksheets.Add
For Each cell In rng
rw = rw + 1
ws.Cells(rw, 1) = cell.Address
ws.Cells(rw, 2) = cell.Comment.Text
Next
End If
End Sub

 

[Jim Rech]

Try this:

Sub ListAllActiveWBComments()
Dim Cell As Range, Rng As Range
Dim WS As Worksheet, RW As Long
On Error Resume Next
Worksheets.Add
For Each WS In Worksheets
Set Rng = Nothing
Set Rng = WS.Cells.SpecialCells(xlCellTypeComments)
If Not Rng Is Nothing Then
For Each Cell In Rng
RW = RW + 1
Cells(RW, 1) = ActiveWorkbook.Name
Cells(RW, 2) = WS.Name
Cells(RW, 3) = Cell.Address
Cells(RW, 4) = Cell.Comment.Text
Next
End If
Next
End Sub


--
Jim Rech
Excel MVP
| Hello --
|
| I had success with the code posted by
|
| Patrick Molloy
| Subject: Re: Search comment with value
| Date: 2002-08-01 11:56:07 PST
|
| to list the comments in a sheet.
|
| Does anyone know of or can you suggest how to embed this code in a loop
| through all sheets in the workbook, so I can document the workbook with
one
| run of a process, rather than having to run the process for each sheet?
|
| I want the new sheet to have the following columns:
| Workbook name
| Worksheet name
| Cell reference
| Comment
|
| Thanks for any help.
|
| Larry Mehl
|
|
| Patrick's code:
| Sub FindComments()
| Dim cell As Range, rng As Range
| Dim ws As Worksheet, rw As Long
| On Error Resume Next
| Set rng = ActiveSheet.Cells.SpecialCells(xlCellTypeComments)
| If rng Is Nothing Then
| MsgBox "No cells with comments were found."
| Else
| Set ws = Worksheets.Add
| For Each cell In rng
| rw = rw + 1
| ws.Cells(rw, 1) = cell.Address
| ws.Cells(rw, 2) = cell.Comment.Text
| Next
| End If
| End Sub
|
|

 

[L Mehl]

Jim --

Thank you very much. Your code lists the information I want, and will save
a lot of time.

Now that you have done the heavy lifting, I will practice my coding skills
and see if I can name the new worksheet, specify width for column D, add
gridlines, and a custom footer.

Larry Mehl