Working with Comment objects in VBA

B

Bruce

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
 
T

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top