Detect Comment + Happy Christmas

  • Thread starter Thread starter Chris Gorham
  • Start date Start date
C

Chris Gorham

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
 
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
 
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/
 
Back
Top