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.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
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
 
Upvote 0
"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.
 
Upvote 0
Fortunately Microsoft provided backwards compatibility - the code I posted was written and tested in Excel 2002.

I'll get my coat...
 
Upvote 0
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:
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,136
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top