List all Threaded Comments in a Workbook

[Thomazz]

Hello all,

I continue to be very confused with the use of Threaded Comments in Excel.

What I'm trying to achieve is having a tabsheet in the workbook (let's call it ControlSheet), which would show in the first column the position of all threaded comments in the rest of the workbook (like cell address) and in the column next to that the actual content of the comment.

Doesn't sound like a hard thing to do in VBA, but can't get it to work. Any ideas ?

 

[sykes]

This should help.
All you need to do is amend the code to work with the modern Comments, as opposed to the "comments" in the code (now referred to as "Notes").

 

[Thomazz]

That works fantastic ! I only have one follow up question : so currently have two columns. One with the address and one with my original comment. How can I have in a third column the reply or replies ? Is there a code for that as well ?

 

[sykes]

Glad that helped.
I've not really used VBA on the new threaded comments, but...
As a starter for 10, you can count the number of comments on a sheet, thus:

activesheet.CommentsThreaded.count

...and using the index number of the comment, extract the text of each reply, thus:

activesheet.CommentsThreaded(1).replies(1).text

You'll have to do a bit of digging to extract more code (quite a bit out there nowdays, if you Google it), but as you can see, you can get to the data you need, and therefore it would be a simple next step to get each reply in the cell of your choosing.
Good luck!

 

[sykes]

I had another look 'round for you, and this might put you closer to the right track...
I'm sure it could easily be adapted to achieve what you wish to do.

 

[Thomazz]

Again, worked like a charm. Thanks sir, superb help !

 

[sykes]

Pleasure, and thanks for the feedback. ?