Excel 2000: Comments

[Guest]

Three questions about comments:

1. Within a loop such as this:

for each cmt in activesheet.comments
... how do I find out the cell in which cmt is?
next

2. Is there a way of finding out if a cell, say the Activecell, has a
comment?

3. The Author property of a comment is read only; is there a way of changing
it other than by deleting it and recreating it? (hence, question 1).

Thanks for your help.

 

[Bob Phillips]

Three questions about comments:

1. Within a loop such as this:

for each cmt in activesheet.comments
... how do I find out the cell in which cmt is?
next

cmt.parent.address


2. Is there a way of finding out if a cell, say the Activecell, has a
comment?

Two ways:

For Each cmt In ActiveSheet.Comments
If Not Intersect(cmt.Parent, ActiveCell) Is Nothing Then
MsgBox "activecell has comment"
End If
Next


On Error Resume Next
MsgBox ActiveCell.Comment.Text
On Error GoTo 0

3. The Author property of a comment is read only; is there a way of changing
it other than by deleting it and recreating it? (hence, question 1).

Don't think so.

 

[Guest]

You can create a range with commented cells:

Dim r, r2 As Range
On Error Resume Next
Set r = Selection.SpecialCells(xlCellTypeComments)

then you can loop on these cells

for each r2 in r
........................
msgbox(r2.address)

next

 

[Dave Peterson]

#1. msgbox cmt.parent.address

#2. if activecell.comment is nothing then
'no comment
else
'has comment
end if

#3. You mean the "prefix" stuff that appears at the front of the comment? Just
what you suggested.

But you can insert a comment (using code) without that name:
http://www.contextures.com/xlcomments03.html#Plain
(From Debra Dalgleish's site)

There's lots of code examples there that you may want to review.

 

[Guest]

Thanks for the replies.

I never thought about using intersect!

I did not mean just the name that appears in the comment itself but the
Author property; Bob has confirmed my own conclusion that it cannot be
changed.