HasComment ?!?

[Tom]

Hi

I'd like to check, if the cell A1 contains a comment. If yes, the comment
should be deleted. But the problem is, that the HasComment-property is not
available. How to do?

Tom

 

[Bob Phillips]

Tom,

Here's a small UDF to do it

Function HasComment(rng As Range)
If rng.Count > 1 Then
HasComment = CVErr(xlErrValue)
Else
HasComment = Not (rng.Comment Is Nothing)
End If
End Function

=HasComment(A1) returns TRUE or FALSE as appropriate
=HasComment(A1:A2) returns #VALUE (more than 1 cell being referenced)

 

[John]

Sub Macro1()
Dim c As Comment

For Each c In ActiveSheet.Comments
If c.Parent.Address = Range("A1").Address Then
c.Delete
End If
Next

End Sub

 

[Tom]

Thanks a lot!

Tom

Sub Macro1()
Dim c As Comment

For Each c In ActiveSheet.Comments
If c.Parent.Address = Range("A1").Address Then
c.Delete
End If
Next

End Sub

 

[John]

Bob,

Posted my suggestion below.

I like your suggestion too. I forgot about "Is Nothing", - good one.

regards,

John

 

[Myrna Larson]

For the user's stated problem, how about just

Sub RemoveA1Comment
On Error Resume Next
Range("A1").Comment.Delete
End Sub