VBA - AddComment

[BuddieB]

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.

[VoG]

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

[Mark O'Brien]

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

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

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.

[VoG]

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

I'll get my coat...

[Mark O'Brien]

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.