HasComment ?!?

  • Thread starter Thread starter Tom
  • Start date Start date
T

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
 
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)
 
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
 
Thanks a lot!

Tom



John said:
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
Click to expand...
 
Bob,

Posted my suggestion below.

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

regards,

John
 
For the user's stated problem, how about just

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