Results 1 to 5 of 5

VBA - AddComment

This is a discussion on VBA - AddComment within the Excel Questions forums, part of the Question Forums category; I want to add comments to a few cells that may or may not already have comments associated with them. ...

  1. #1
    Board Regular
    Join Date
    Aug 2003
    Location
    Boston
    Posts
    174

    Default VBA - AddComment

    I want to add comments to a few cells that may or may not already have comments associated with them.

    Is there a way to check if there is a comment already attached to a cell and if so can i just add my new text to the existing comment text?

    Thanks.

  2. #2
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,460

    Default Re: VBA - AddComment

    Something like this - it doesn't check for an existing comment but is easily modified to do so:

    Sub AddToCmt()
    Dim OldCmt As String, NewCmt As String
    NewCmt = "add this"
    OldCmt = ActiveCell.NoteText
    NewCmt = OldCmt & " " & NewCmt
    ActiveCell.NoteText NewCmt
    End Sub

  3. #3
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530

    Default Re: VBA - AddComment

    "NoteText" has been superceded by Cell Comments in Excel 97 onwards.

    You may want to consider using something like the following example:

    Code:
    Public Sub main()
      Dim oRange As Range
      Dim oComment As Comment
      Dim sText As String
      
      'Use object variable to hold range.  In this example Sheet1 Cell A1
      Set oRange = Sheets("Sheet1").Range("A1")
      'Use object variable for comment
      Set oComment = oRange.Comment
      'text to be added to the comment box
      sText = "Hello"
      
      'If Sheet1 Cell A1 has a comment then appeng "Hello" to the end of the comment text
      If Not oComment Is Nothing Then
        sText = oComment.Text & sText
        oRange.Comment.Delete
      End If
    
      'Add a comment with the contents of sText
      oRange.AddComment sText
    End Sub
    This checks to see if Cell A1 on Sheet1 contains a comment. If it does then the text "Hello" is appended to the original text. If there is no comment in the cell then a comment is added with the text "Hello".

    If you run this code once, with no comment in A1, the comment will read "Hello". If you run the code twice, the comment in A1 will read "HelloHello".

    Hope this helps.

  4. #4
    VoG
    VoG is online now
    MrExcel MVP
    Moderator
    VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,460

    Default Re: VBA - AddComment

    Fortunately Microsoft provided backwards compatibility - the code I posted was written and tested in Excel 2002.

    I'll get my coat...

  5. #5
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530

    Default Re: VBA - AddComment

    Actually, "NoteText" is probably easier to work with in this case, but who knows when Microsloth will stop being so kind with their backwards compatability.


Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com