Forum Discussion

Jan Auzins's avatar
Jan Auzins
Copper Contributor
Apr 05, 2019

Excel Cell Comments/Notes in VBA

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

office 365

7 Replies

Sort By
  • TechNicCal's avatar
    TechNicCal
    Copper Contributor
    Oct 22, 2019

    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?

    • GregInOxford45056's avatar
      GregInOxford45056
      Copper Contributor
      Feb 13, 2021

      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.

       

       

      • JeromeSmith's avatar
        JeromeSmith
        Copper Contributor
        Sep 07, 2021

        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

  • DataVision's avatar
    DataVision
    Copper Contributor
    Sep 30, 2019

    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

     

Resources