Checking Threaded Comments

Question

My office just upgraded to a new version of Office 365, and with it came a new way that comments are done. The old comments are now referred to as "notes" and the new comments are now called "comments".

In one of my workbooks, I have a button that, when clicked, will look through certain cells to check whether there is a comment or not. It will then color the cell based on what it finds.

(my full code for the button is posted below) Line 9 contains the issue

In previous versions of Excel, this button worked just fine. However, now it only works if the cells have "notes" and does not work if they have "comments". In my code, the class that I had been using was called "Comment", so my code was something along the lines of "If Cells(row, col).Comment Is Nothing...". This class still works, but only looks for notes. I have looked through the Object Library and under the hidden objects, I found a new class called "CommentThreaded". I tried changing my code to that ("If Cells(row, col).CommentThreaded Is Nothing...") but it does not work. When I click the button, I now get a run-time error: applictaion-defined or object-defined error when it tries to access this new class.

Does anyone know what I need to change to get my button to work with threaded comments?

Thanks, Mike

Sub Comments()
Dim xrow As Integer
Dim xcol As Integer

For xrow = 7 To 88
    For xcol = 3 To 15
        If Cells(xrow, xcol).Value <= -0.1 Or Cells(xrow, xcol).Value >= 0.1 Then
            If Cells(5, xcol).Value = "MTD %" Or Cells(5, xcol).Value = "YTD %" Then
                If Not Cells(xrow, xcol).Comment Is Nothing Then
                    Cells(xrow, xcol).Interior.Color = RGB(155, 255, 188)
                Else
                    Cells(xrow, xcol).Interior.Color = RGB(255, 255, 0)
                End If
            End If
        End If
    Next xcol
Next xrow
End Sub

Answer 1

As of May 15th 2019 the new object CommentThreaded is described by Microsoft.
In my Excel version 1906, it's fully supported in VBA.

Your assumed If Range.CommentThreaded Is Nothing works.

Here's some code to play with:

Private Sub ExcelsNewCommentThreaded()
    Dim AllCommentsThreaded As Excel.CommentsThreaded
    Dim OneCommentThreaded As Excel.CommentThreaded
    Dim AllReplies As Excel.CommentsThreaded
    Dim OneReply As Excel.CommentThreaded
    Dim r As Range

    Set AllCommentsThreaded = ActiveSheet.CommentsThreaded

    ' loop over all threaded comments of a worksheet and get their info
    For Each OneCommentThreaded In AllCommentsThreaded
        With OneCommentThreaded
            Debug.Print .Author.Name, .Date, .Text
            For Each OneReply In .Replies
                With OneReply
                    Debug.Print .Author.Name, .Date, OneReply.Text
                End With
            Next OneReply
        End With
    Next OneCommentThreaded

    Set r = Selection.Cells(1)

    ' check if the selected cell already contains a threaded comment
    If r.CommentThreaded Is Nothing Then
        r.AddCommentThreaded ("my new comment")
    End If

    With r.CommentThreaded
        ' get text of comment
        Debug.Print .Text

        ' add some replies
        .AddReply ("my reply 1")
        .AddReply ("my reply 2")

        ' change text of comment
        Debug.Print .Text(Text:="text of comment changed")
        Debug.Print .Text

        ' change text of a reply
        .Replies(1).Text Text:="text of reply 1 changed"
        Debug.Print .Replies(1).Text

        ' delete second reply
        .Replies(2).Delete

        ' delete whole comment including its replies
        .Delete
    End With
End Sub