How to determine if a comment already exists? (Excel 97 SR-2)

Topic

New Reply

Aloha all,

I’m looking for a VBA code snippet to determine if a comment already exists in a cell before I add one. Seems simple, but I’m spinning my wheels.

Mahalo in advance for your comments on comments.

JohnJ

Reply | Quote

Replies

The comments object is part of the range of a given worksheet. Cycle through this object for a given worksheet to test if it has any comments or to add/remove comments. If you write a comment to a cell that already has one, it’ll replace the previous comment. Here’s code that works in xl2000 that determines if for a selected range if there are comments:

Public Function HasComment(myRng As Range) As Boolean
Dim cmt As Comment, rng As Range

Debug.Print “# of comments on sheet: ” & ActiveSheet.Comments.Count
For Each cmt In ActiveSheet.Comments
Debug.Print cmt.Text
Next cmt

‘ get range of cells in worksheet that have comments
Set rng = myRng.SpecialCells(xlCellTypeComments)
If rng.Cells.Count > 0 Then
If Not Intersect(rng, myRng) Is Nothing Then
Debug.Print “Yes, there are comments in range: ” & myRng.Address
Else
Debug.Print “No comments in range: ” & myRng.Address
End If
End If

End Function

You can edit it for any worksheet and replace the debug statements with real code.

HTH, Deb

Reply | Quote

I hit POST IT too soon… The code works but has an extra, unnecessary line in it. Once you find the range of cells that have comments in the given worksheet, you don’t need to do the “if rng.cells.count” I just added that before I did the Intersect, not needed. Do with it as you will.

Deb

Reply | Quote

Thanks Guy,

That’s the one. Thanks also, Deb, for the extra goodies.

JohnJ

Reply | Quote