Comment or not

[Alan]

Is there an easy way within VBA to determine whether a give cell on a
worksheet has a comment associated with it or not?

Alan

 

[Bob Phillips]

If Not ACtivecell.Comment Is Nothing Then
MsgBox "has coment"
....

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[paul.robinson]

Hi
What most people do is

On error resume next
myCell.AddComment
On Error Goto 0

If there is no comment on the cell then one is added, and if one
exists then nothing happens. Now simply carry on with whatever you
want to do with the comment.

If you really need to know that a comment exists or not try

Public Function DoesCommentExist(myCell As Range) As Boolean
Dim myComment As Shape
DoesCommentExist = False
Err.Clear
On Error Resume Next
Set myComment = myCell.Comment.Shape
If Err.Number = 0 Then DoesCommentExist = True
End Function

Run this macro on the activecell with and without a comment. Sub
tester()
MsgBox DoesCommentExist(ActiveCell)
End Sub

Interestingly, Set myComment = myCell.Comment does not work and always
returns true.
regards
Paul

 

[Alan]

If Not ACtivecell.Comment Is Nothing Then
MsgBox "has coment"
...

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)







- Show quoted text -

Thanks Bob, that was just what I needed. I was using "On Error" as
Paul was suggesting but I have so many cases to consider my "GoTos"
were getting a bit complicated.

Alan

 

[Bob Phillips]

Interestingly, Set myComment = myCell.Comment does not work and always
returns true.

What do you mean Paul? If myComment is a Comment object, that code will
either return Nothing or the comment object.

 

[paul.robinson]

What do you mean Paul? If myComment is a Comment object, that code will
either return Nothing or the comment object.

Hi Bob,
Just me not thinking it through. I thought Set myComment =
myCell.AddComment would raise an error if myCell had no comment, but
it doesn't of course, it just returns Nothing. The code was inside an
On Error... block so I couldn't see the none existent error!
regards
Paul