Excel Cell Comments/Notes in VBA

[Jan Auzins]

I am trying to retrieve Note/Comment Cell text using VBA. I have no problem getting the text for a cell note but I have not found a way to get the text for a cell comment.

Example code.

str = Worksheets(1).Range("A1").Comment.Text

works fine if the cell contains a note but not if it contains a comment.

Is there a way to retrieve the comment text?

Regards

Jan Auzins

[#2]

@Jan Auzins 

Hi!

Add "Threaded" to "Comment"

old:

str = Worksheets(1).Range("A1").Comment.Text

new:

str = Worksheets(1).Range("A1").CommentThreaded.Text

Hope this helps

DataVision

[#3]

@DataVision That's great, thank you.

[#4]

Along similar lines, does any know how to extract notes (not comments) from cells? I've searched the net endlessly and found a formula for =getcomment(b2) for example, but this does not work. I specifically want to extract notes from cells in timesheets where a number of hours are listed and have the notes for that time output in other cells that I designate for the notes. I've watched YouTube videos but for some reason it is not working. I use O365 so I am on the most current version of Excel. Can anyone help?

[#5]

@DataVisionadding the "threaded" did work but it only grabs the first comment. Any suggestions when cells have multiple comments in them?

[#6]

@TechNicCal 

I was looking to do the same thing as you (i.e. list all of the comments in a worksheet.)  My problem was the same as others who discovered that Excel now has a new threaded category called "Comments" and the old category that used to be called "Comments" is now called "Notes"

If you wish to list all of the NOTES in an Excel 365 worksheet, the following URL does a great job of explaining, and even has a downloadable .xlsm spreadsheet containing examples, the code itself, and even a button to invoke the macro.

https://trumpexcel.com/get-list-of-comments-in-a-worksheet-excel/

(I don't think it has anything to do with the former president  https://trumpexcel.com/about/ )

Now I am looking to modify that macro to get the actual threaded COMMENTS.  To see the difference, download his spreadsheet, and  run the macro.  Then modify one of the NOTES  (he calls them comments)  Run the macro again, and you will see the change.  THEN go to a cell and and add a COMMENT (not a note) and you'll see that the macro isn't picking it up.

[#7]

@GregInOxford45056 

Try this

Public Sub Convert_Comments_to_Notes()
'Converts the Office 365 comments into notes.
Dim rng As Range, cell As Range
Dim comments As String
Set rng = Selection

For Each cell In rng
If Not cell.CommentThreaded Is Nothing Then
comments = cell.CommentThreaded.Text
cell.CommentThreaded.Delete
cell.AddComment (comments)
End If
Next cell
End Sub