Home > Macros > Basics > Threaded Comments Excel Threaded Comment MacrosUse 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. |
-- Macro - List Threaded Comments
-- Macro - List Threaded Comments & Replies
-- Macro - Number and List Comments
-- Macro - Remove Numbers
-- Macro - Threaded Comments & Replies to Word
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.
If a cell contains a threaded comment or a note, it usually has a coloured indicator at the top right corner of the cell.
To see an individual note or threaded comment:
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.
To show all the threaded comments on the active sheet, follow these steps:
To see the comments in the task pane, follow these steps:
You can change the size and position of the comments task pane.
To adjust the task pane width:
To move the task pane:
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
Run this ShowThreadedComments macro, to open the Comments Task Pane
Sub ShowThreadedComments() Application.CommandBars("Comments") _ .Visible = True End Sub
Run this HideThreadedComments macro to close the Comments Task Pane
Sub HideThreadedComments() Application.CommandBars("Comments") _ .Visible = False End Sub
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
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.
-- Macro - List Threaded Comments
-- Macro - List Threaded Comments & Replies
-- Macro - Number and List Comments
-- Macro - Remove Numbers
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.
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
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
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
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
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
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.
Last updated: May 3, 2023 1:17 PM