On 2013/07/18 04:47 PM, Tanstaafl wrote:
> 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?
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
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: firstname.lastname@example.org
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
- Re: [libreoffice-users] Calc - allow users to format cells on protected sheets · Errol Goetsch
Impressum (Legal Info)
: 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