Using comments as worksheet review

Hi all,

I am trying to automating the review process of the workbooks using comments.

What I am trying to do is:

- After the user name, I want to include 3 categories for the comments: "Open", "Cleared" and "Closed". With this classification, I can control whether the comment has been reviewed or not.

For example, whenever I include a comment (I know that I will not be able to use the standard button on excel), the first time the comment should be classified as "Open" (E.g. UserName (Open): ). After a review from someone else, this person should change the comment to "Cleared" (E.g. UserName (Cleared): ) and after the revision of the response, the final user will change the comment to "Closed" (E.g. UserName (Closed): ). I also do not want to loose the text inserted in the comments and following Follow-ups.

Additionally, does anyone know how to list dinamically the comments in a sheet? This should help the reviewing process.

Thanks in advance!!!

Diego Sant'Ana

Hi Leith Ross,

Thank you for the quick repply.
See attached the example on what I am trying to do.

Regards,

Hi Leith,
The intention is to the user insert any response from the question/comment by typing the response.
Thank you,

Hi Leith,

If I understood your question, yes, the sample workbook should list the comments with the same fields as the example and the responses can be displayed toghether with the all the comment text in one cell. There is no need of splitting the question/comment and response into 2 different cells.

The main intention is to have a sheet where the reviewer can see which comments have already been cleared/explained and give the final answer - to close the comment.

Regards,

Hi Leith,

Thanks for the code. Basically is that what I was thinking about, with a few modifications. My main problem is to write a code where the string after the user name (Open, Cleared and Closed) gets updated automatically without loosing the comment text.

The way I was thinking how to manage the comments is the following, creating 4 separated macros where I can point to 4 buttons in the ribbon (I know how to do that):

a.) Macro 1: "Open note"
b.) Macro 2: "Cleared note"
c.) Macro 3: "Close note"
d.) Macro 4: "List review notes"

For each macro:

Macro 1 - creating the review note and adding after the Username the "(Open)" string or,
change the "status" back to "(Open)" - sometimes, there are still something to be reviewed by the user, instead of deleting
and creating a new comment, just changing the added string "(Cleared)" or "(Closed)" back to "(Open)" again.

Macro 2 - replace the "(Open)" or "(Closed)" strings to "(Cleared)" and maintaining the text inside.

Macro 3 - replace the "(Open)" or "(Cleared)" strings to "(Closed)" and maintaining the text inside.

Macro 4 - create a new worksheet where all the comments from the workbook will be listed as explained before

I just want to list the "Open, cleared and closed" comments there is no need to include all comments, those created without running the macros above can be discarted)

I was wondering if it is possible to automate the update on the worksheet whenever I include a new comment using the macros above

Thanks again for the help.

Hi Leith,

See the answers bellow:

- Yes, you are correct. Thatīs the ideia to create a new comment on the active cell.

- Yes, I believe this is the best way to deal with no comment on the active cell. The user should be notified to click "Open note" fisrt.

- At first, It will be just fine changing the added string (Open, cleared and closed), whitout changing the original Username. This way, it will be possible to track who created the comment and the responses to that comment.


I do not know how complex is to insert multiple headers, but it will be nice to have the name of the user who cleared and closed the comment later in the process. For example: adding just the user who changed the status of the comment (changes in underline/bold):
Open note click: "Diego (Open): Where is Waldo?"
Cleared note click: "Diego (Cleared): Where is Waldo? Leith: He's next to the big tree."
Closed note click: "Diego (Closed): Where is Waldo? Leith: He's next to the big tree. Closed by: USERNAME"

Thanks again for the help!!!

Hi Leith,

Just to get an uptade from you. Would you be able to help me on the request above? If not please let me know. Iīll try to get the code another way.

Thanks,