Forum Discussion
Excel Cell Comments/Notes in VBA
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.
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