I am writing some code to pick up the comments off my worksheet and dump as a
summary on a new sheet. I am doing this with the text property. However I
have the following questions.

1) Can I remove the Author name from the Text and just reveal the message?

2) Can I determine the date the comment was updated.

3) Can I determine the cell address the comment comes from?

See below....

Sub myComments()

Application.ScreenUpdating = False

MyMarket = "China"
Set cmt = Worksheets(MyMarket).Comments

'Clear old
Sheets("Comments").Range("2:5000").ClearContents

myRow = 2

For Each c In cmt
Sheets("Comments").Range("C" & myRow) = c.Text
myRow = myRow + 1
Next

Application.ScreenUpdating = True

End Sub

Sub myComments()
Dim MyMarket as String, sStr as String
c as Comment, myRow as Long, iloc as Long
Application.ScreenUpdating = False

MyMarket = "China"
Set cmt = Worksheets(MyMarket).Comments

'Clear old
Sheets("Comments").Range("2:5000").ClearContents

myRow = 2

For Each c In cmt
With Sheets("Comments").Range("C" & myRow)
sStr = c.Text
iloc = Instr(sStr,":")
if iloc < 0 then
sStr = Trim(Right(sStr,len(sStr)-iloc))
.Value = sStr
.offset(0,-1).Value = c.parent.Address(0,0)
myRow = myRow + 1
Next

Application.ScreenUpdating = True

End Sub

To the best of my knowledge, comments don't record data on when they were
updated.

--
Regards,
Tom Ogilvy

"Bruce" wrote in message
...
I am writing some code to pick up the comments off my worksheet and dump
as a
summary on a new sheet. I am doing this with the text property. However I
have the following questions.

1) Can I remove the Author name from the Text and just reveal the message?

2) Can I determine the date the comment was updated.

3) Can I determine the cell address the comment comes from?

See below....

Sub myComments()

Application.ScreenUpdating = False

MyMarket = "China"
Set cmt = Worksheets(MyMarket).Comments

'Clear old
Sheets("Comments").Range("2:5000").ClearContents

myRow = 2

For Each c In cmt
Sheets("Comments").Range("C" & myRow) = c.Text
myRow = myRow + 1
Next

Application.ScreenUpdating = True

End Sub

Dim c As Comment
Dim Comment As String

Set c = ActiveCell.Comment

Comment = c.Text

Debug.Print "Question 1: " & Replace(Comment, c.Author & ":"
vbCrLf, "")

Debug.Print "Question 3: " & c.Parent.Addres

--
Nick
-----------------------------------------------------------------------
Nicke's Profile: http://www.excelforum.com/member.php...nfo&userid=293
View this thread: http://www.excelforum.com/showthread.php?threadid=26945

I may have missed it. Did you mention this would not work in Excel 97?


--
Regards,
Tom Ogilvy


"Nicke" wrote in message
...

Dim c As Comment
Dim Comment As String

Set c = ActiveCell.Comment

Comment = c.Text

Debug.Print "Question 1: " & Replace(Comment, c.Author & ":" &
vbCrLf, "")

Debug.Print "Question 3: " & c.Parent.Address


--
Nicke
------------------------------------------------------------------------
Nicke's Profile:
http://www.excelforum.com/member.php...fo&userid=2930
View this thread: http://www.excelforum.com/showthread...hreadid=269452