Insert a comment with a protected cell 1

[jmjj215]

How can I allow the user to insert comments into unlocked cells when the sheet is protected? I've noticed that when the sheet is protected, the insert comment option is disabled. Anyway to disable the disabling?

 

[PaulTEG]

activesheet.unprotect if no password is given, though I think if this is called and a password is present it will prompt the user for it

HTH
--Paul


It's important in life to always strike a happy medium, so if you see someone with a crystal ball, and a smile on their face ...

 

[jmjj215]

Well, I can't simply unprotect it, because then the user could change anything. I want to leave it protected but allow the user to add comments.

 

[PaulTEG]

You could write a macro to unprotect the sheet, insert a comment on the relevant cell, and then activesheet.protect but that would again be without a password, which they could disable through the menu.

What info do you not trust your users with? Maybe Excel isn't the best choice for this particular application
--Paul

It's important in life to always strike a happy medium, so if you see someone with a crystal ball, and a smile on their face ...

 

[fumei]

If the user wants to add a comment, and the sheet is protected, train them to click a macro button on the toolbar...any toolbar. Or maybe add it as a menu item "Add Comment", whatever.

Clicking that button, or selecting the menu, will (and of course you will have to make this happen), open a simple userform with a textbox, and a commandbutton.

They type in their comment, press OK. Your code will run, and while it is running the user will not be able to do anything.

Your code? Unprotects the sheet, inserts the comment from the textbox, reprotects the sheet. Done. User will never have access while code is running. That is, of course, assuming the userform is modal. This is also assuming:

1. There is a password to protect.
2. You know it.
3. The users do not.

Gerry

 

[LeighAnne]

Gerry,

Although i'm not the originator of this post, i've been having the exact same problem as jmjj215. Thanks for the post, i've used a userform as you suggested and it works like a dream.

Le