How can I use conditional formatting to protect a cell in LibreOffice Calc?

Engineer2 · May 8, 2019, 7:28pm

Hi guys,

Or do you have another method to produce this effect in LibreOffice Calc 5.4.5.1 in Windows 10?

Here’s an example:

Type 1: 10 12 14

Type 2: 8 2 8

Type 3: 10 5 15

Protect: 1 1 0

I would like to use conditional formatting so that when a “1” is typed into the Protect line, all cells of that column (including the cell to Protect column itself) changes status to Protected. That would be programmed into each cell’s conditional formatting.

I can change the color of a cell with conditional formatting, but so far I am unable to force it to become protected by some automated method.

The goal is for a user to type values, and once the user is certain that the values are ok, he can lock all cells of that column with a single keystroke. That prevents accidental modification of that column later on. The lock also applies to the last line, so the user cannot remove its own lock once it has been activated.

I would also need the reverse idea: if a 0 is typed into the Protect line, all cells of that column become unprotected. That would only be doable by a user with the protected sheet password, so it would still be ok.

Any ideas?

I am using the French version of LibreOffice, but I posted in English because I thought I would get more replies.

Lupp · May 8, 2019, 8:01pm

Did you consider that cell protection only works in protected sheets?

Remove the attribute Protected from the default cell style.
Create a cell style inheriting from default, but having the attribute Protected again.
Define your conditional format as needed using the above mentioned style.
4. Protect the sheet.

If you now make true the condition of the CF, the cells of the respective range will be protected.
If a cell you had to edit to make the condition TRUE is part of the protected range itself, you cannot undo the change.

I would dissuade from using such a convoluted construct. It may be prone to bugs and to unwanted side effects.

===Edit1 2019-05-08 21:00 UTC regarding the points by the OQ in his comment posted as an answer===
OQ: "However, once I unlock the sheet and check the properties of that cell, it doesn’t have a check next to “Protected”.
… and a cell coloured red by CF wouldn’t have the respective colour attribute.
That’s how CF works: The conditions are only evaluated for cells inside the view, and the attributes set for the styles to be conditionally overlaid are not assigned to any cell but only shown in the cell grid / put in force in the visible range. This is not exactly optimal concerning protection, and that’s one of the reasons for what I called the “construct convoluted”.
OQ: “However, LibreOffice Calc’s conditional formatting does not allow more than 1 condition to be true at the same time.”
Of course LibO cannot make a TRUE condition FALSE, but it can only overlay one style. This is the first one (by top-down order) associated with a condition coming out TRUE. (If it’s not about protection: How should a second colour be overlaid?) If you want to influence more than one attribute, you need “conditional styles” and the respective compound conditions for as many cases as shall be distinguished.
OQ: “At this point, what do you think? Am I using the wrong tool for the job?”
How should I tell? I know by far too little about your job.
OQ: “…and use some background VBA to perform some of those tasks?”
VBA is an MS-specific programming toy, and only in parts (error-prone) supported by LibO. But LibO comes with a powerful, though slightly uncommon API (and a Basic … and bridges to more than one programming tool). However, needing a lot of custom programming should be suspected to be an indícation of either having chosen the wrong software (move to a DB then e.g.) or having made design decisions without sufficient consideration … or of being in pursuit of something not practically feasible.
OQ: "A friend told me that it might be possible to use some kind of terminal interface to input into a database… "
Your friend, knowing probably more about your needs, should explain his (f/m) proposal thoroughly to you, and go into more detail concerning possible tools/implementations. I don’t sufficiently understand for what reason he is talking about a terminal interface, e.g.
Anyway: Databases have big advantages in many cases, and protection of data (in more than one context) often is among them, but CF is not a main feature of DB, afaik.

Engineer2 · May 10, 2019, 12:38pm

I am very impressed by the wisdom and experience of your reply. I learned a lot from it, including the fact that conditional formatting only changes the visible part of the cell, it does not assign different properties to the cells. And that a database would be unlikely to allow conditional formatting which is an important feature of this project.

I’m a chemical engineer with good computer skills (but not a professional programmer) so I had to seek help on this. A colleague has programmed a macro to do this conditional cell protection for me. Thank you for your help!

Engineer2 · May 8, 2019, 8:48pm

I confess that I am willing to give up on the idea that putting zero on the last line could unlock cells. However, being able to lock a column with a single typed character (1) would be very interesting for users with low levels of computer skills.

I tried your method. Somehow, changing the sheet’s default style to “not protected” and having my “protected” style under it partially works:

Once I lock the sheet and type 1 in that cell, the cell itself locks.

That also works if I typed a “1” while the sheet was unlocked and re-lock it.

However, once I unlock the sheet and check the properties of that cell, it doesn’t have a check next to “Protected”.

That’s very weird, what do you think?

I have realized a significant issue with this way of doing things.

In my sheet, I also use conditional formatting to color cells based on content entered by the user (if the value entered is way too low, I put red and if it’s only slightly too low, it’s yellow, etc).

However, LibreOffice Calc’s conditional formatting does not allow more than 1 condition to be true at the same time. Once the first “true” condition in a list of conditions is applied, it’s over, which means that I cannot have the “protected” status automatically applied to a cell which is already yellow or red by conditional formatting…

https://help.libreoffice.org/Calc/Conditional_Formatting

At this point, what do you think? Am I using the wrong tool for the job? Should I start back this program into Microsoft Excel and use some background VBA to perform some of those tasks?

The endgame goal is that the user enters the information from a Windows 10 tablet into a file on a local server.

A friend told me that it might be possible to use some kind of terminal interface to input into a database… maybe that’s the solution?

Lupp · May 8, 2019, 8:58pm

Please do not use the “answer” facility if not you actually answer a (your own probably) question. To give additional information you can edit your question (hopefully without spoiling the traces of the history). To comment on an answer or to ask for additional explanation … you should use the “add a comment” tool.