Date: prev next · Thread: first prev next last
2013 Archives by date, by thread · List index

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 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

To unsubscribe e-mail to:
Posting guidelines + more:
List archive:
All messages sent to this list will be publicly archived and cannot be deleted


Privacy Policy | Impressum (Legal Info) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License. This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License (MPLv2). "LibreOffice" and "The Document Foundation" are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy.