Working with Comment objects in VBA

[Guest]

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

 

[Tom Ogilvy]

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.

 

[Nicke]

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

 

[Tom Ogilvy]

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