VBA - AddComment

BuddieB

Board Regular
Joined
Aug 6, 2003
Messages
174
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.
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
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

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530
"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.
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,651
Fortunately Microsoft provided backwards compatibility - the code I posted was written and tested in Excel 2002.

I'll get my coat...
 

Mark O'Brien

MrExcel MVP
Joined
Feb 15, 2002
Messages
3,530
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.

:LOL:
 

Watch MrExcel Video

Forum statistics

Threads
1,089,943
Messages
5,411,414
Members
403,368
Latest member
Cyril18

This Week's Hot Topics

Top