How to determine if a cell has a comment

Hi all, Can anybody tell me how to find out if a cell has a comment attached with it. Using VBA,:- Msgbox activecell.comment.text works ok providing that the cell has a comment. If it hasn't, run-time error 91 is generated. Thanks for any help. John

Assign the comment to an Object  then test the Object.  Here's an example from Microsoft:

 Sub Has_Comment()
       Dim mycomment As Object

       Set mycomment = ActiveCell.Comment
       If mycomment Is Nothing Then
           MsgBox "no comment in cell"
       Else
           MsgBox mycomment.Text
       End If
   End Sub

 

I have this as a function which returns true or false if the nominated cell has a comment, like this:

Public Function HasComment(ByRef rng As Excel.Range) As Boolean
    On Error Resume Next Dim cmt As Excel.Comment
    Set cmt = rng.Comment 
    If Not cmt Is Nothing Then 
         HasComment = True
    End If
End Function

 

Regards - Marcus

You can also use Goto (F5), Special, Comments to select all comments on a sheet. Which, when I record it comes up as a single line in VBA

Selection.SpecialCells(xlCellTypeComments).Select

You can probably automate from there.

Hi,

 

Or you could make a function out of it...

 

Public Function HasComment(rngRef As Range) As Boolean
    On Error Resume Next
    HasComment = CBool(Len(rngRef.Comment.Text))
End Function