Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Excel: Does comment exist? 1

Status
Not open for further replies.

groleau

Programmer
Apr 12, 2006
110
US
What is the secret incantation to use for this?
Code:
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
 
A crude way is to play with the On Error instruction.

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



Hi,

How bout this...
Code:
Dim com As Comment
For Each com In ActiveSheet.Comments
    MsgBox com.Text
Next

Skip,

[glasses] [red][/red]
[tongue]
 


Range does not have a HasComment property.

Here's a function that does that, however...
Code:
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,

[glasses] [red][/red]
[tongue]
 
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
 



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

Skip,

[glasses] [red][/red]
[tongue]
 
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
 
Use this:

If Cells(x,y).comment Is Nothing Then
...
End If
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top