Excel: Does comment exist? 1

[groleau]

What is the secret incantation to use for this?

With Cells(x,y)
    If .HasComment then
         DoSomething(.Comment.Text)
    End If
End With

<range>.HasComment doesn't exist

Comment = Nothing is "invalid use of object"

Several other guesses got the same

Not checking gets "Ob Var or With not set"

--
Wes Groleau

http://www.Parkview.com/

 

[PHV]

A crude way is to play with the On Error instruction.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

How bout this...

Dim com As Comment
For Each com In ActiveSheet.Comments
    MsgBox com.Text
Next

Skip,

_[red][/red]

 

[SkipVought]

Range does not have a HasComment property.

Here's a function that does that, however...

Function HasComment(Target As Range) As Boolean
    Dim com As Comment
    HasComment = False
    For Each com In ActiveSheet.Comments
        If Not Intersect(com.Parent, Target) Is Nothing Then
            HasComment = True
            Exit For
        End If
    Next
End Function

Skip,

_[red][/red]

 

[groleau]

Thanks, folks!

Looping through the sheet's comments won't work--I have to
loop through the cells and do this if applicable.

Since the user won't see it, might go with "crude but less typing"



thanks again

--
Wes Groleau

http://www.Parkview.com/

 

[SkipVought]

You can use my HasComment function as you loop thru your cells.

Skip,

_[red][/red]

 

[groleau]

Thanks, Skip.

That's almost what I did. Since "Do Something" deals
with the text of the comment, I made the function
return the text or an empty string.

--
Wes Groleau

http://www.Parkview.com/

 

[rmoster]

Use this:

If Cells(x,y).comment Is Nothing Then
...
End If

 

[Bong]

I think the point is that the comments collection, each object of which is a comment, belongs to the worksheets class, not the range class.

_________________
Bob Rashkin