Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Apr 2001
    Location
    Honolulu, HI
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Guy Havers
    Guest

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

    Hello John

    I've used this in the past...

    If Range("A1").Comment Is Nothing Then

    etc etc

    Hope this helps

    Guy

  3. #3
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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 <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

  4. #4
    5 Star Lounger jujuraf's Avatar
    Join Date
    Jun 2001
    Location
    San Jose, California, USA
    Posts
    1,061
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  5. #5
    New Lounger
    Join Date
    Apr 2001
    Location
    Honolulu, HI
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Thanks Guy,

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

    JohnJ

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •