Working With Comments in VBA

To start with let's understand that I'm talking about the 'old' style comments like the one on the left.

In Office 365 we now have Threaded Comments which are also being referred to as comments, with the old style comments being referred to as Notes in O365.

But if you are not using O365 then you will still be using the old style comments and still calling them comments. That's not confusing now is it!

Download Example Workbook

Examples of all the code in this post can be downloaded from the sample workbook

The Comment Object

To work with comments in VBA you'll be mostly using the comment object.

This allows you to do things like delete a comment, change the comment text, or find out things like the comment author or the cell where the comment is.

Adding a Comment

To add a comment you actually use the AddComment method of the Range object

Note: Creating a comment this way results in a comment without the author's name appearing as it would if you inserted the comment manually.

Delete a Comment

Use the Delete method on the comment.

Deleting Specific Author's Comments from Sheet

You can specify the author by passing a string parameter when you call the Sub, rather than hard code the author.

Deleting all Comments on a Sheet

You can specify the author by passing a string parameter when you call the Sub, rather than hard code the author.

Deleting all Comments in the Workbook

Loop through every comment, on every sheet

Find Comments by Author

This will change the color of the cells where a comment exists from the specified author.

Show or Hide Comment Indicator

Comment Indicator Only

Comment and Indicator

No Indicator

Change the Background Color of the Comment

Change comment background to green.

List All Comments in Workbook

This routine will create a sheet called Comments and then list every comment from every sheet on it.

You can get this code by downloading the example workbook from the top of the post.