Excel Threaded Comments and Replies List Details-Macro

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!

threadedcommentmacros02

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?

threadedcommentmacros01

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!

cmt28

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.

threadedcommentmacros12

Macros to List Comments

If you click the purple button, a macro runs, and lists each main comment, with its details,  as shown below.

threadedcommentmacros08

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.

threadedcommentmacros13

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

Excel Threaded Comments and Replies List Details-Macro

_____________________

Original source: https://contexturesblog.com/archives/2023/02/16/excel-threaded-comments-and-replies-list-details-macro/?utm_source=rss&utm_medium=rss&utm_campaign=excel-threaded-comments-and-replies-list-details-macro

Close Menu