Re: [libreoffice-users] Calc - allow users to format cells on protected sheets

On 2013/07/18 04:47 PM, Tanstaafl wrote:
 > Hello,
 > Microsoft Excel has the ability to allow users to apply formatting > 
changes to protected cells when a sheet is protected.
 > Is there any possible way to do this in Calc?
>  Thanks

Yes, using conditional formats. Protecting the cell _and_ the sheet 
together normally locks out all changes, but a conditional format can 
override both and unlock the cell (but not for formats alone).

In the example at 
https://docs.google.com/file/d/0B6LXy9sguZVkeE8wTFgyZ2JQcW8/edit?usp=sharing
1. d2 and d3 are both Format Cells|Cell Protection|Protected (this is to 
show that locked cells can be opened)
2. c2 and c3 are both Format Cells|Cell Protection|unprotected (this is 
to change their value so as to trigger the condition)
3. sheet "Protect" is Tools|Protect Document|Sheet|Select unprotected cells
4. 2 styles exist; "Protect", which is red background and cell 
protected, and "Unprotect" which is blue background and not.
5. d2 and d3 also have conditional formats. so that when the adjacent 
cell is a particular value (here 1), the Protect style applies, when the 
cell is not 1, the Unprotect style applies.

The effect of this is that when c3 = 1 (or some password of your 
choice), d3 can be selected|format changed even though the sheet and d3 
are protected.
This way achieves the intended result, with the possibly unintended 
result of opening d3's value to change as well, which undoes the 
intention of protection.
A half-way is to use conditional formatting to switch automatically 
between styles based on pre-set criteria, giving visual diversity 
without creating edit risk.

PS In this example, the trigger value is transparent when the cell is 
unlocked (the user can see the conditional formatting formula), but you 
can make the trigger refer offsheet to a value the user can't see.
Hope this helps
Errol




--
To unsubscribe e-mail to: users+unsubscribe@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted