Detect Comment + Happy Christmas
 

Greetings fellow VBA geeks and a happy Christmas

anybody know the syntax for detecting if a cell contains a
comment or not...??

Let's hope that Beagle 2 is just messing about and forgot
to phone home for a laugh...

Rgds

Chris

Detect Comment + Happy Christmas
 

Chris,

Try something like


Dim Cmt As Comment
On Error Resume Next
Set Cmt = ActiveCell.Comment
If Cmt Is Nothing Then
MsgBox "No comment"
Else
MsgBox "Has comment: " & Cmt.Text
End If
On Error GoTo 0



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Chris Gorham" wrote in
message ...
Greetings fellow VBA geeks and a happy Christmas

anybody know the syntax for detecting if a cell contains a
comment or not...??

Let's hope that Beagle 2 is just messing about and forgot
to phone home for a laugh...

Rgds

Chris

Detect Comment + Happy Christmas
 

Hi,

Try this,

Function DetectComment(rng As Range) As Boolean
On Error Resume Next
Dim cmtcell As Range
If rng.Count = 1 Then
If Not rng.Comment Is Nothing Then
DetectComment = True
Err.Clear
Exit Function
End If
Else
Set cmtcell = rng.SpecialCells(xlCellTypeComments)
Err.Clear
End If
If Not cmtcell Is Nothing Then
DetectComment = True
End If
End Function

Sub Test()

If DetectComment(Selection) Then
MsgBox "Comment Found"
Else
MsgBox "Comment Not Found"
End If
End Sub

Regards,
Shah Shailesh
http://members.lycos.co.uk/shahweb/


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!