Results 1 to 9 of 9
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Determining if a Comment is present (2003/SP2)

    I've been using comments to flag cells with errors in a massively messed up spreadsheet.
    Now, I'm double checking my work and want to remove the comments.

    I can determine which cells don't have comments using something like
    If activecell.comments is nothing

    However, I'm having a complete brain fade for finding when a cell does have a comment.

    Any suggestions?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determining if a Comment is present (2003/SP2)

    Wrap your check for a comment with a Not( )

    Sub IsThereAComment()
    If Not (ActiveCell.Comment Is Nothing) Then
    MsgBox "Comment"
    Else
    MsgBox "No comment"
    End If
    End Sub

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Determining if a Comment is present (2003/SP2)

    If <font color=red>not</font color=red> activecell.comment is nothing then

    Note: there is no "s" in comment

    Steve

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Determining if a Comment is present (2003/SP2)

    Thanks shortly after posting I got
    if (activecell.Comment is nothing)=false

    Amazing how asking a question prompts the answer to pop up sometimes
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Determining if a Comment is present (2003/SP2)

    In case anyone is interested in the finished product
    Sub stripcomments()
    Dim xCell As Range
    For Each xCell In Selection
    If Not (xCell.Comment Is Nothing) Then
    xCell.Comment.Delete
    End If
    Next xCell
    End Sub
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Determining if a Comment is present (2003/SP2)

    In case anyone is interested, if you want to delete all comments in the entire sheet, a better approach is not to select a range, but to use the comments object directly:

    <pre>Option Explicit
    Sub DeleteComments()
    Dim cmt As Comment
    For Each cmt In ActiveSheet.Comments
    cmt.Delete
    Next
    End Sub</pre>


    Steve

  7. #7
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Determining if a Comment is present (2003/SP2)

    As a lateral thinking idea for getting rid of comments,
    the Paste Special function in Open Office allows you to choose what features you wish to include/exclude when doing cut and paste.

    Open your spreadsheet in Open Office, select all worksheets, Ctrl A, Ctrl C, Ctrl N, Ctrl+Shift+V, clear Paste All, clear Notes, tick Numbers, OK, Save (under a new name).
    Attached Images Attached Images

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Determining if a Comment is present (2003/SP2)

    And just as another variation:
    <pre>Sub ClearComments()
    ActiveSheet.UsedRange.ClearComments
    End Sub
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Determining if a Comment is present (2003/SP2)

    <P ID="edit" class=small>(Edited by mbarron on 27-Nov-07 21:12. Forgot "[OK] out")</P>Another (non macro) method to get rid of all comments on a sheet:
    F5 (or Ctrl+G)
    [Special...] button
    Select Comments
    [OK] out
    Right click in any cell that contains a comment
    Choose Delete Comment

Posting Permissions

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