HasComment ?!?
 

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

HasComment ?!?
 

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)

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Tom" wrote in message
...
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

HasComment ?!?
 

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" wrote in message
...
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

HasComment ?!?
 

Thanks a lot!

Tom



"John" <john.iseppi.iinet.net.au schrieb im Newsbeitrag
...

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" wrote in message
...
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

HasComment ?!?
 

Bob,

Posted my suggestion below.

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

regards,

John


"Bob Phillips" wrote in message
...
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)

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks


"Tom" wrote in message
...
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

HasComment ?!?
 

For the user's stated problem, how about just

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


On Fri, 22 Aug 2003 18:37:33 +1000, "John" <john.iseppi.iinet.net.au wrote:


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" wrote in message
...
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