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

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)



"Alan" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>

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


On Oct 24, 11:31 am, Alan <alan.do...@virgin.net> wrote:
> 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

On 24 Oct, 11:40, "Bob Phillips" <bob....@somewhere.com> wrote:
> 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)
>
> "Alan" <alan.do...@virgin.net> wrote in message
>
> news:(E-Mail Removed)...
>
>
>
> > 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- Hide quoted text -
>
> - 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

<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...

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

On Oct 24, 3:03 pm, "Bob Phillips" <bob....@somewhere.com> wrote:
> <paul.robin...@it-tallaght.ie> wrote in message
>
> news:(E-Mail Removed)...
>
> > 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.

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