Disabling notes/comments using VBA

[Rob Wood]

Hi There,

I have an Excel with multiple notes/comments that I don't want all users to be able to edit or see. Whilst I can turn comments and indicators off using VBA code, they can easily be "turned back on" using Show Comments or Show Notes in the Ribbon.

Is there a way to password-protect the comments at all, or disable the Comments/Notes options in the Ribbon for users?

I've seen mention of a custom UI editor, but not sure I follow that approach.

Looking for some advice.

Thanks,

Rob.

[nankw83]

Hello … I am not really sure if that's doable but you can save all comments in a very hidden sheet then restore them when needed using the 2 below macros

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[Rob Wood]

Hi nankw83 - Thanks for responding.

I tried using your code for hiding comments on my Sheet2 on workbook open; however, it errors out at the ReDim (Subscript out of range).

Any suggestions here?

Thanks,

Rob.

[nankw83]

It could be that there are no comments in "ActiveSheet" when you open the file … Try the below code & see if it errors out

Please Login or Register  to view this content.

[nankw83]

And you can do the same for the UnHide macro

Please Login or Register  to view this content.

[Rob Wood]

Using the revised VBA code, I'm no longer getting the error message, but it's also not hiding the comments on Sheet2. There are definitely 5 comments on Sheet2.

Any further suggestions here?

Thanks,

Rob.

[nankw83]

Sheet2 is the sheet name or sheet code name ? Did you change it in the code ?

[Rob Wood]

I did change the code to reflect Sheet2. Sheet2 is the code name, the sheet name is "Allocation". I ran the code step-by-step and it simply exits the sub suggestion there are no comments on Sheet2 when there definitely is...

Thanks,

Rob.

[nankw83]

Do you have comments or notes ? The code works for notes not comments

[CK76]

Rob, I'd recommend uploading sample workbook to clearly demonstrate your need. It'll be so much easier for us to help you when we have appropriate sample to work with.

[nankw83]

Hey Rob .. So I had some free time at home due to coronavirus curfew so it was a good opportunity to learn about Excel's new "comment" in VBA. I have revised the code significantly to accommodate for all comment & notes in all sheets of the workbook instead of only one sheet. I have also attached the sample file I was working on for your reference

VBA code for hiding all comments/notes

Please Login or Register  to view this content.

VBA code to show back all comments/notes

Please Login or Register  to view this content.