VBA to protect specfic objects on a worksheet

Hi,

I have a spreadsheet that is password protected and has numerous cells which are locked, however, it also has cells that contain comments which we want people to be able to Edit so the "Edit Objects" option is checked.

The problem is I also have some buttons (shapes, not ActiveXControls) which have various macros assigned to them which the users click to filter, run reports etc.

The issue is that when I password protect the sheet people can edit my buttons. They have to right click over them to select them but they can then type over and move etc.

1)Is there a way to protect these specific objects?
2) If so how would this work? i.e. would it protect them on opening by the user?
3) Then, how would I, as the designer, then access these objects?

Thanks
MissDB

Good afternoon MissDB

The only way to protect these objects is using the Review > Protect Sheet function, which will prevent you from entering comments. You are going to have a hard time using VBA to give these items any form of protection against user tinkering.

A workaround might be to keep the worksheet protection invoked, but have a button, or hot key combination that will allow the user to enter a comment to the currently active cell through an input box or simple userform. The macro would need to collect the comment from the user (via userform / input box), turn off protection, post the comment and then re-protect the sheet.

Is that acceptable?

HTH

DominicB

After a lot of hard work we decided to remove the buttons. We did not have time to look this alternative solution, however it's look a good idea so may use this elsewhere or as part of further development if we get time. Thank you!