I don't see anything where you're keeping track of when the comments were
added. And that's gonna make the solution much more difficult than it has to
be. (I think you'd have to keep the historical comments, compare addresses of
the comment's cell and then compare the comment itself--sounds like a problem
just waiting to happen!)

Personally, if I have a choice between using comments and using cells, I'll use
cells. There's lots of things you could do based on cells (autofilter/sorting)
that become a problem with comments.

I think I'd use cells to contain the info. In fact, I'd add another column that
included the date that the "comment" was added. Then you could retrieve any set
of comments you wanted.

And you could have an even macro fire each time you changed a cell in a certain
column that would put the date in another column.

J.E. McGimpsey shows how at:
http://www.mcgimpsey.com/excel/timestamp.html

Kenshe wrote:

Dave,

Thanks for the macro, it really works great! The other macro from your
suggested site is a little more problematic for what I am trying to
achieve. I have been working with the one you posted and I am having a
little trouble in modifying it.
It works great and is ready to use, but I notice I needed to generate
the "comment" report by date. I wanted to generate a new report every
day leaving out the previous comments already generated the day before.
Another words, the current comments for that day are the only coments
being reported. For once I create a report and print it, I delete the
created report page (sheet 1) for that day. I dont want to keep
reporting the old comments along with the new.

Here is the modified macro I am using:

Private Sub CommandButton1_Click()
Dim cmt As Comment
Dim wks As Worksheet
Dim rptWks As Worksheet
Dim DestCell As Range
ActiveWorkbook.Unprotect
Set rptWks = Worksheets.Add
With rptWks
Range("a1").Resize(1, 3).Value _
= Array("Sheet", "Location", "Comment")
Set DestCell = rptWks.Range("a2")
End With

With ActiveSheet.Range("C1")
ColumnWidth = 600 / .Width * .ColumnWidth
End With

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = rptWks.Name Then
'do nothing
Else
For Each cmt In wks.Comments
DestCell.Value = "'" & wks.Name
DestCell.Offset(0, 1).Value = cmt.Parent.Address(0, 0)
DestCell.Offset(0, 2).Value = cmt.Text
Set DestCell = DestCell.Offset(1, 0)
Next cmt
End If
Next wks
Sheets("Comment Rpt").Select
ActiveWorkbook.Protect Structu=True, Windows:=False
End Sub

Private Sub CommandButton2_Click()

'
' SheetDelete Macro
' Macro recorded 12/9/2005 by Oliver User
'

'
Sheets("Sheet1").Select
ActiveWorkbook.Unprotect
ActiveWindow.SelectedSheets.Delete
ActiveWorkbook.Protect Structu=True, Windows:=False
End Sub

Maybe it would be better not use the "comment" window and just enter
any needed comments in a cell and use a bunch of "if then" statements
to do what I need with more avenues of attack??

Thanks again for all your help!

Ken

--
Kenshe
------------------------------------------------------------------------
Kenshe's Profile: http://www.excelforum.com/member.php...o&userid=28850
View this thread: http://www.excelforum.com/showthread...hreadid=486081

--

Dave Peterson