Keep track of Excel worksheet discussions, with a detailed list of threaded comments and replies. Just click a button, to run a macro, that makes the list for you. And I’ve fixed the macro!
Threaded Comments in Excel 365
If you use Excel 365, there’s a fancy version of comments – threaded comments. This is the default comment style now, instead of those little yellow pop-up comments from older versions of Excel.
When you add a new comment now, in Excel 365, people can reply to your comment, and a “thread” is created.
In the screen shot below, you can see a threaded comment, with my original comment, and a reply below it.
And yes, I talk to myself – frequently!
Threaded Comments and Notes
You can still create the old-style comments in Excel 365, but they’re called “Notes” now.
In the screen shot below, you can see that:
- old-style comments (Note) have a red triangle at the top right of the cell
- threaded comment cells have larger purple shapes at the top right of the cell
What is that purple shape? I think it’s a rectangle, with a triangle below it, at the bottom right.
Does that shape represent a speech bubble?
Old-Style Comment Details Macro
On my Contextures site, long ago, I created macros that list the old-style comments on a worksheet.
You can even put tiny numbers over the red triangles, then print a list of the numbered comments!
Threaded Comment Details Macro
Threaded comments were introduced in 2018, and the old macros didn’t work with these new comments.
So, I eventually created a new set of macros, to list all the threaded comments on a worksheet.
- Note: There’s built-in command to print the threaded notes, but I prefer a different format for my comment lists.
Macro Buttons in Sample File
If you download the threaded comment macro sample file, the main worksheet has macro buttons.
These are rounded rectangles, in random colours, with a macro assigned to each button.
These buttons make it easy to test all the different macros, and see which ones work best for you.
Macros to List Comments
If you click the purple button, a macro runs, and lists each main comment, with its details, as shown below.
There’s also a macro that lists all comments and their replies, as shown in the section below.
Fixed the Excel Macro
The List Comments & Replies macro has been on my site for about three years, and someone finally let me know that it wasn’t working correctly.
It had the following problem:
- If a main comment had more than one reply, all of its replies were listed
- If a main comment had only one reply, that reply was not listed.
Can you guess the error that I had in my code?
Here’s the list, after I fixed the code, and you can see how I fixed the macro, in the next section.
How I Fixed the Macro
After getting an email from John Stolz (Thanks!), about the missing replies, I checked the code.
Fortunately, the problem was easy to spot!
At the start of one section, I had this line:
If myCmt.Replies.Count > 1 Then
Oops! No wonder the one-reply comments had a problem.
So, I changed the 1 to a zero, so the threads with 1 reply would have replies printed too:
If myCmt.Replies.Count > 0 Then
Then I tested the code, and it works correctly now.
Get the Sample File
Excel 365: If you’re using Excel 365, and you’d like test the macros, go to my Threaded Comments page, and head to the download section.
Other Excel: If you’re using a version of Excel that has old-style comments, use the macros from my Excel Comments VBA page.
_____________________
Excel Threaded Comments and Replies List Details-Macro
_____________________