PDA

View Full Version : [SOLVED] Tracking Comments



reza_doang
06-28-2010, 07:25 PM
Hi All,

i have a workbook that will share to anyone and i got macro from this group about tracking changes with comment.
Now, i have a workbook with many sheets and maybe have many changes with comment, so i thing i need macro that will create a new sheet and pull out all comments in new sheet.
i attach my sample, in that sample i have 2 sheet and comments.
after run macro, it will create a new sheet with sheet name "comments".
for more detail you can see the attachment.

many thanks for your help

reza

Aussiebear
06-28-2010, 09:58 PM
Try the following section of code from Debra Dalgliesh's website http://www.contextures.com

List Comments on New Sheet

Sub showcomments()
'posted by Dave Peterson 2003-05-16
Application.ScreenUpdating = False
Dim commrange As Range
Dim cmt As Comment
Dim curwks As Worksheet
Dim newwks As Worksheet
Dim i As Long
Set curwks = ActiveSheet
On Error Resume Next
Set commrange = curwks.Cells _
.SpecialCells(xlCellTypeComments)
On Error GoTo 0
If commrange Is Nothing Then
MsgBox "no comments found"
Exit Sub
End If
Set newwks = Worksheets.Add
newwks.Range("A1:E1").Value = _
Array("Number", "Name", "Value", "Address", "Comment")
i = 1
For Each cmt In curwks.Comments
With newwks
i = i + 1
On Error Resume Next
.Cells(i, 1).Value = i - 1
.Cells(i, 2).Value = cmt.Parent.Name.Name
.Cells(i, 3).Value = cmt.Parent.Value
.Cells(i, 4).Value = cmt.Parent.Address
.Cells(i, 5).Value = Replace(cmt.Text, Chr(10), " ")
End With
Next cmt
newwks.Cells.WrapText = False
newwks.Columns.AutoFit
Application.ScreenUpdating = True
End Sub

This should give you a good start.

reza_doang
06-28-2010, 10:29 PM
aussie...

work great...
btw i want to ask little, what for the column name?
why i get empty for that?

thanks

Aussiebear
06-29-2010, 02:25 PM
"What for the column name"? & "Why I get empty for that"?

Can you explain in greater detail please, I've just come off a 14 hour shift, so go easy.