VBA - AddComment

B

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

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
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
You must log in or register to reply here.

Similar threads

edge37
  • Solved
Inserting more than one comment in a cell
Replies
4
Views
321
edge37
edge37
M
  • Solved
Stack comments with VBA instead of clear and overwrite
Replies
8
Views
269
MeaclH
M
D
  • Question Question
Automatically add cell contents of specific range to cell comments when opening a document
Replies
3
Views
160
saboh12617
S
M
  • Question Question
Sheet code runtime error 1004
Replies
3
Views
179
MeaclH
M
M
  • Question Question
Where to protect sheet in this code?
Replies
1
Views
89
Micron
M

Forum statistics

Threads
1,224,119
Messages
6,176,489
Members
452,730
Latest member
palsmith

Share this page

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