Home > Macros > Basics > Threaded Comments Excel Threaded Comment Macros Use these Excel threaded comment macros to list all threaded comments, list all comments and replies, and show or hide all comments comments. Get the free workbook in the download section. NOTE: In newer versions of Excel, old-style comments are called Notes. For Notes macros, go to the Excel Comments VBA page.

Introduction

In Excel for Office 365, there are Threaded Comments, and the macros on this page are designed to work with those comments. For an overview of Threaded Comments, see the notes and warning on the Comment Tips page.

• For macros that work with older comments (Notes), go to the Excel Comments VBA page.

Show a Threaded Comment

If a cell contains a threaded comment or a note, it usually has a coloured indicator at the top right corner of the cell.

• Purple - Threaded comment
• Red - Note (old-style comment)

To see an individual note or threaded comment:

• Point to a cell with a red or purple indicator in its top right corner
• Notes pop up, and can be moved. See the Excel Comments Basics page for tips on working with notes.
• Threaded comments pop up beside the cell, and cannot be moved
• NOTE: If the comments task pane is open, threaded comments will not pop up beside the cell.

Threaded Comments Task Pane

Before we look at macros for threaded comments, here are a few tips for working with the threaded comments task pane. This is where the threaded comments appear, when you choose to show all comments.

Show All Comments

To show all the threaded comments on the active sheet, follow these steps:

1. On the Excel Ribbon, click the Review tab
2. In the Comments group, click Show Comments
• 
3. A task pane opens, showing all the threaded comments
   • 

See Comments in Task Pane

To see the comments in the task pane, follow these steps:

• Use the scroll bar at the right side of the task pane, to move up and down in the list of threaded comments
• To see a specific comment, click its cell on the worksheet. That comment gets the focus in the task pane.
• If you click on a comment in the task pane, its cell is automatically selected on the worksheet

Adjust the Task Pane

You can change the size and position of the comments task pane.

To adjust the task pane width:

• Point to the left side of the task pane.
• When the pointer changes to a 2-headed arrow, drag left or right, to change the width.

To move the task pane:

• Point to the task pane header, where the title, arrow and X are located
• When the pointer changes to a 4-headed arrow, drag the task pane to a different location on the worksheet.

Show Comments Macros

Instead of using the Show Comments command to open and close the Comments task pane, you can use the following 3 macros.

-- ShowThreadedComments - open the comments task pane

-- HideThreadedComments - open the comments task pane

-- ToggleThreadedComments - if open, close task pane; if closed, open it

Show Comments Macro

Run this ShowThreadedComments macro, to open the Comments Task Pane

Sub ShowThreadedComments()
Application.CommandBars("Comments") _
  .Visible = True
End Sub

Hide Comments Macro

Run this HideThreadedComments macro to close the Comments Task Pane

Sub HideThreadedComments()
Application.CommandBars("Comments") _
  .Visible = False
End Sub

Toggle Comments Macro

Run this ToggleThreadedComments macro to switch the Comments Task Pane from open to closed, or from closed to open

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

In the code, the Visible property is changed to the opposite of its current setting

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

List Threaded Comments Macros

There is an option to include comments when printing an Excel worksheet, but the results aren't too useful. Instead, use this macro to create a formatted Excel table, with details on all the threaded comments. Then, print the list that the macro created.

-- Built-in Comment Printing

-- Macro - List Threaded Comments

-- Macro - List Threaded Comments & Replies

-- Macro - Number and List Comments

-- Macro - Remove Numbers

Built-in Comment Printing

If you print an Excel sheet, you can go into the Page Setup, and on the Sheet tab, choose to print the threaded comments at the end of the sheet.

NOTE: The option to print comments as displayed on the sheet is only available for Notes, not threaded comments.

This screen shot of the Print Preview shows how the printed comments will look. It's a long list of the comments and replies.

Macro List Threaded Comments

Instead of the built-in comment printing, use this macro to create a list of threaded comments, with details. Then, print that list.

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

Macro List Comments & Replies

Instead of the built-in comment printing, use this macro to create a list of threaded comments, with their replies, and comment details. Then, print that list.

NOTE: This example has 3 columns for replies, but the macro will add more columns, if needed.

Sub ListCommentsRepliesThreaded()
Application.ScreenUpdating = False

Dim myCmt As CommentThreaded
Dim myRp As CommentThreaded
Dim curwks As Worksheet
Dim newwks As Worksheet
Dim myList As ListObject
Dim i As Long
Dim iR As Long
Dim iRCol As Long
Dim ListCols 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
   If myCmt.Replies.Count > 0 Then
    iR = 1
    iRCol = 7
    For iR = 1 To myCmt.Replies.Count
      .Cells(1, iRCol).Value = "Reply " & iR
      .Cells(i, iRCol).Value _
        = myCmt.Replies(iR).Author.Name _
          & vbCrLf _
          & myCmt.Replies(iR).Date _
          & vbCrLf _
          & myCmt.Replies(iR).Text
      iRCol = iRCol + 1
    Next iR
   End If
   End With
Next myCmt

With newwks
.ListObjects.Add(xlSrcRange, _
  .Cells(1, 1) _
    .CurrentRegion, , xlYes) _
    .Name = ""
End With

Set myList = newwks.ListObjects(1)
myList.TableStyle = "TableStyleLight8"
ListCols = myList.DataBodyRange _
  .Columns.Count

With myList.DataBodyRange
  .Cells.VerticalAlignment = xlTop
  .Columns.EntireColumn.ColumnWidth = 30
  .Cells.WrapText = True
  .Columns.EntireColumn.AutoFit
  .Rows.EntireRow.AutoFit
End With

Application.ScreenUpdating = True

End Sub

Macro Number & List Comments

Instead of the built-in comment printing, use this macro to add a small number in each threaded comment cell, over the purple indicator. The macro also creates a numbered list of threaded comments, with details. Then, print that list.

Sub NumberAndList()
Dim ws As Worksheet
Dim wsL As Worksheet
Dim cmt As CommentThreaded
Dim myList As ListObject
Dim ListCols As Long
Dim lCmt As Long
Dim cmtCount As Long
Dim rngCmt As Range
Dim shpCmt As Shape
Dim shpW As Double 'shape width
Dim shpH As Double 'shape height

shpW = 8
shpH = 6
lCmt = 1

Set ws = ActiveSheet
cmtCount = ws.CommentsThreaded.Count

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

'clear any existing numbers
RemoveIndicatorShapes

Set wsL = Worksheets.Add
wsL.Range("A1:H1").Value = _
    Array("Number", "Sheet", "Cell", _
     "Author", "Date", "Replies", _
     "Text", "Resolved")

For Each cmt In ws.CommentsThreaded
  Set rngCmt = cmt.Parent
  With rngCmt
    Set shpCmt = ws.Shapes _
      .AddShape(msoShapeRectangle, _
      rngCmt.Offset(0, 1) _
        .Left - shpW, .Top, shpW, shpH)
  End With
  With shpCmt
    .Name = "CmtNum" & .Name
    With .Fill
      'white
      .ForeColor.SchemeColor = 9
      .Visible = msoTrue
      .Solid
    End With
    With .Line
      .Visible = msoTrue
      'automatic
      .ForeColor.SchemeColor = 64
      .Weight = 0.25
    End With
    With .TextFrame
      .Characters.Text = lCmt
      .Characters.Font.Size = 6
      .Characters.Font _
        .ColorIndex = xlAutomatic
      .MarginLeft = 0#
      .MarginRight = 0#
      .MarginTop = 0#
      .MarginBottom = 0#
    .HorizontalAlignment = xlCenter
    End With
    .Top = rngCmt.Top + 0.001
  End With
  
  With wsL
    On Error Resume Next
    .Cells(lCmt + 1, 1).Value _
        = lCmt
    .Cells(lCmt + 1, 2).Value _
        = rngCmt.Parent.Name
    .Cells(lCmt + 1, 3).Value _
        = rngCmt.Address
    .Cells(lCmt + 1, 4).Value _
        = cmt.Author.Name
    .Cells(lCmt + 1, 5).Value _
        = cmt.Date
    .Cells(lCmt + 1, 6).Value _
        = cmt.Replies.Count
    .Cells(lCmt + 1, 7).Value _
        = cmt.Text
    .Cells(lCmt + 1, 8).Value _
        = cmt.Resolved
  End With

  lCmt = lCmt + 1
Next cmt

With wsL
.ListObjects.Add(xlSrcRange, _
  .Cells(1, 1) _
    .CurrentRegion, , xlYes) _
    .Name = ""
End With

Set myList = wsL.ListObjects(1)
myList.TableStyle = "TableStyleLight8"
ListCols = myList.DataBodyRange _
  .Columns.Count

With myList.DataBodyRange
  .Cells.VerticalAlignment = xlTop
  .Columns.EntireColumn.ColumnWidth = 30
  .Cells.WrapText = True
  .Columns.EntireColumn.AutoFit
  .Rows.EntireRow.AutoFit
End With

Application.ScreenUpdating = True

End Sub

Macro Remove Numbers

After you use the Number & List Comments macro, you can run this macro, to remove all the numbers from threaded comment cells.

Sub RemoveIndicatorShapes()

Dim ws As Worksheet
Dim shp As Shape

Set ws = ActiveSheet

For Each shp In ws.Shapes
  If Left(shp.Name, 6) = "CmtNum" Then
    shp.Delete
  End If
Next shp

End Sub

Threaded Comments & Replies to Word

The following code copies the threaded comment and replies text from the active sheet, and adds them to a Microsoft Word document, along with the cell address, author, and date/time.

Sub CopyThreadedCommentsAndRepliesToWord()
'https://www.contextures.com
'  /excelthreadedcommentmacros.html
Application.ScreenUpdating = False

Dim myCmt As CommentThreaded
Dim myRp As CommentThreaded
Dim curwks As Worksheet
Dim i As Long
Dim iR As Long
Dim iRCol As Long
Dim ListCols As Long
Dim cmtCount As Long
Dim WdApp As Object

Set curwks = ActiveSheet
cmtCount = curwks.CommentsThreaded.Count

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

On Error Resume Next
Set WdApp = GetObject(, "Word.Application")
If Err.Number <> 0 Then
  Err.Clear
  Set WdApp = CreateObject("Word.Application")
End If

With WdApp
  .Visible = True
  .Documents.Add DocumentType:=0
    .Selection.TypeText ActiveWorkbook.Name
    .Selection.TypeParagraph
    .Selection.TypeText ActiveWorkbook.Path
    .Selection.TypeParagraph
    .Selection.TypeParagraph
  
  i = 1

  For Each myCmt In curwks.CommentsThreaded
     On Error Resume Next
      .Selection.TypeText _
        i & ": " _
          & myCmt.Parent.Address _
             & vbTab & myCmt.Author.Name _
             & "; " & myCmt.Date _
             & "; " & "Replies: " _
              & myCmt.Replies.Count
      .Selection.TypeParagraph
      
      .Selection.TypeText _
        vbTab & myCmt.Text
      .Selection.TypeParagraph
     
      If myCmt.Replies.Count > 0 Then
       iR = 1
       For iR = 1 To myCmt.Replies.Count
        .Selection.TypeText _
          vbTab & _
            "Reply " & iR & ": " _
              & myCmt.Replies(iR).Author.Name _
              & "; " & myCmt.Replies(iR).Date
        .Selection.TypeParagraph
              
        .Selection.TypeText _
          vbTab & vbTab & _
            myCmt.Replies(iR).Text
        .Selection.TypeParagraph
       
         iRCol = iRCol + 1
       Next iR
      End If
    i = i + 1
  Next myCmt

End With

Set WdApp = Nothing

Application.ScreenUpdating = True

End Sub

Download Sample File

To test the macros, and see the sample threaded comments, download the Threaded Comments Macros sample file. The zipped file is in xlsm format, and contains the macros from this page. Be sure to enable macros when you open the workbook, if you want to test them.

More Tutorials

Excel Comments Basics

Excel Comments Macros

Add Comments in a Pivot Table