Excel Threaded Comments Macros

Written by Debra Dalgleish from Contextures Blog

In Excel for Office 365, there are threaded comments, where you can have discussions with your co-workers. Or, reply to your own threaded comments, with updates on the data. The old-style comments are now called Notes, and they still work the way they always did. Here are a couple of macros that you can use to work with the new Excel threaded comments.

Threaded Comment Macros

The macros in this post are designed to work with the new Excel threaded comments only.

If you need macros for the comment (Notes) in older versions of Excel, see the Excel Comment Macros page on my Contextures site.
For more details on the differences between threaded comments and old-style comments (now called Notes), see this explanation on the Microsoft site.

See a Threaded Comment

If a cell has a threaded comment, there’s usually a purple indicator in the top right corner of the cell. (Indicators can be turned off in Excel Options)

When you point to that cell, the threaded comment appears, starting at the top left corner of the next cell.

threadedcommentmacros02

See All Threaded Comments

On the Excel Ribbon’s Review tab, there is a Show Comments command, in the Comments group. The next group is for Notes – the old-style comments.

The first time you click the Show Comments command, a Comments Task Pane opens. In the task pane, you can:

see all the existing threaded comments, and their replies.
edit, delete and reply to the comments, or create new comments.

threadedcommentmacros04

If the Comments Task Pane is open:

threaded comments will not pop up beside the cell
you can click the Show Comments again, to close the task pane

Macro to Show/Hide Task Pane

Instead of using the Show Comments command to open and close the Comments task pane, you can use the following macro to show or hide it.

In the code, “Not” changes the Visible property to the opposite of its current setting

Sub ToggleThreadedComments()
With Application.CommandBars("Comments")
  .Visible = Not .Visible
End With
End Sub

In the sample file, there is a button on the worksheet to run the ToggleThreadedComments macro

threadedcommentmacros05

Macro to List All Threaded Comments

If you want to store the comments, or analyze them, use this macro to make a list of all the threaded comments on the active sheet, with a few details.

The list has these 6 columns:

sequential numbers
cell address
author
date
number of replies
comment text

threadedcommentmacros08

Code to List All Comments

Here is the code for the List All Comments macro. Copy the code, and put it in a regular code module in your workbook.

Sub ListCommentsThreaded()
Application.ScreenUpdating = False

Dim myCmt As CommentThreaded
Dim curwks As Worksheet
Dim newwks As Worksheet
Dim i As Long
Dim cmtCount As Long

Set curwks = ActiveSheet
cmtCount = curwks.CommentsThreaded.Count

If cmtCount = 0 Then
   MsgBox "No threaded comments found"
   Exit Sub
End If

Set newwks = Worksheets.Add

 newwks.Range("A1:F1").Value = _
     Array("Number", "Cell", "Author", _
      "Date", "Replies", "Text")

i = 1
For Each myCmt In curwks.CommentsThreaded
   With newwks
     i = i + 1
     On Error Resume Next
     .Cells(i, 1).Value = i - 1
     .Cells(i, 2).Value = myCmt.Parent.Address
     .Cells(i, 3).Value = myCmt.Author.Name
     .Cells(i, 4).Value = myCmt.Date
     .Cells(i, 5).Value = myCmt.Replies.Count
     .Cells(i, 6).Value = myCmt.Text
   End With
Next myCmt

With newwks
  .Columns(6).ColumnWidth = 50
  .Columns.AutoFit
  With .Cells
    .EntireRow.AutoFit
    .VerticalAlignment = xlTop
    .WrapText = True
  End With
End With

Application.ScreenUpdating = True

End Sub

More Threaded Comment Macros

There are more macros on the Excel Threaded Comment Macros page on my Contextures site. For example:

List all Comments and Replies
Number and List Comments
Remove Numbers

threadedcommentmacros10

Get the Sample File

To get the code for these macros, and to see more threaded comment macro examples, go to the Excel Threaded Comment Macros page.

There is sample code on the page, which you can copy and paste into your workbook. Or, go to the Download section on that page, and get the sample file. It has sample threaded comments for testing, and all of the macros. The zipped workbook is in xlsm format, and contains the macros from the page.

_____________________________________