Excel 2000: Comments

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.

"AA2e72E" <AA2e72E@discussions.microsoft.com> wrote in message
news:A2DDF19C-9B91-4AB6-B2FA-47484787A55D@microsoft.com...
> 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.

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
--
Gary's Student


"AA2e72E" wrote:

> 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.
>

#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.

AA2e72E wrote:
>
> 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.

--

Dave Peterson

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.