List all Threaded Comments in a Workbook

Thomazz

New Member
Joined
Dec 28, 2017
Messages
19
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 ?
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Dec 28, 2017
Messages
19
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

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
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:
VBA Code:
activesheet.CommentsThreaded.count
...and using the index number of the comment, extract the text of each reply, thus:
VBA Code:
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

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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.
 

sykes

Well-known Member
Joined
May 1, 2002
Messages
1,785
Office Version
  1. 365
Platform
  1. Windows
Pleasure, and thanks for the feedback. 👍
 

Watch MrExcel Video

Forum statistics

Threads
1,128,088
Messages
5,628,603
Members
416,327
Latest member
Chimay

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top