Bug Hunting Session
Bug 51636 - Conditional FORMATTING: style element "Cell Protected" and "Language" ignored
Summary: Conditional FORMATTING: style element "Cell Protected" and "Language" ignored
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
(earliest affected)
Inherited From OOo
Hardware: x86 (IA32) Windows (All)
: medium normal
Assignee: Markus Mohrhard
Depends on:
Reported: 2012-07-01 17:20 UTC by yandal
Modified: 2013-11-27 23:58 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:

Sample Document (9.54 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-07-01 21:53 UTC, Rainer Bielefeld Retired
Sample with conditional language (10.29 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-07-01 22:17 UTC, Rainer Bielefeld Retired
Sample Document #3 for Bug 51636 (10.56 KB, application/vnd.oasis.opendocument.spreadsheet)
2012-07-02 03:59 UTC, yandal

Note You need to log in before you can comment on or make changes to this bug.
Description yandal 2012-07-01 17:20:09 UTC
Table is not protected
Cell has status "protection off"

If I create a new cell style with "protection on" and
set a regular condition as trigger to switch,
the cell will not be blocked for input (and selection, if set in protection dialog). All other features of the new style (e.g. changed background color)
are working properly.

Also other way round:
Table is not protected
Cell has status "protection on"

If I create a new cell style with "protection off" and
set a regular condition as trigger to switch,
the cell is still blocked for input (and selection, if set in protection dialog).
All other features of the new style (e.g. changed background color) 
again are working properly.

It seems that the conditional formatting ignores any changes regarding cell protection.
That's very annoying, because it blocks an option I really need.

Is this bug known already (If it will be tracked as a bug ...) ?
I read in many forums, that users are complaining this for years, (OO & LO)
but I didn't find an answer why, whether it's tracked or whether it is intended to be fixed.

Thanks in advance for comments !

Comment 1 yandal 2012-07-01 17:24:02 UTC
Of course I turn the table to protected mode each time before testing
the conditional formatting, and yes ... automatic recalculating is on.
Comment 2 Rainer Bielefeld Retired 2012-07-01 21:51:48 UTC
[Reproducible] with "LibreOffice 3.3.3  German UI/Locale [OOO330m19 (Build:301) tag libreoffice-] on German WIN7 Home Premium (64bit) 

Steps how to reproduce:
0. download open attached Sample documents with styles "unprotected" and 
   "protected", what has sheet protection on
1. Try to modify A3 to contents "x" by typing an "x" into it
   Expected: not possible, because cell should be protected
   Actual: Cell contents is not protected                                !!!
2. Try to modify E10 (source for cell style) to contents "x" by typing an 
   "x" into it
   Expected: not possible, because cell should be protected
   Actual: as expected
3. Try to modify H10 (style "protected has been assigned) to contents "x" 
   by typing an "x" into it
   Expected: not possible, because cell should be protected
   Actual: as expected

E12 and H12 can be modified as expected.

Result of that tests:

As Expected: When I assign a style to a cell, 
             the style element "Cell protection" will be respected
Unexpected:  When I assign a style to a cell by Conditional Formatting, 
             the style element "Cell protection" will NOT be respected   !!!

Currently I have no idea for a "real life application" where this might be important, so only "Normal"

"Real life application"?

What do you think?
Comment 3 Rainer Bielefeld Retired 2012-07-01 21:53:05 UTC
Created attachment 63686 [details]
Sample Document

See Comment 1
Comment 4 Rainer Bielefeld Retired 2012-07-01 22:17:58 UTC
Created attachment 63687 [details]
Sample with conditional language

New sample shows that also language assigning by conditional formatting will not work correctly. For tests please remove sheet protection.

H10 has been switched to "English" (to check type "car") as expected by stile assignation.

For test in column A copy/paste A2:A20 as plain text to the same area. Auto spell check will underline all words, although yellow background cells should be "English USA"
Comment 5 yandal 2012-07-02 03:59:56 UTC
Created attachment 63698 [details]
Sample Document #3 for Bug 51636
Comment 6 yandal 2012-07-02 04:01:32 UTC
(In reply to comment #2)

> @yandal@safe-mail.net:
> "Real life application"?
If you want to give users a chance to override automaticly calculated
containing of a cell without unprotecting the whole sheet, it's necessary
to get this feature working.

So, to answer your question: possible real life application (not finalized,

Sample file created and attached

Steps how to reproduce:
0. Download and open attached sample document
   Single sheet with sheet protection on
1. Insert a value in A5
2. Answer given question by choosing "Yes" in the list of E11
3. Try to insert any other value in A7
Expected: possible, because cell protection should be disabled
Actual: not possible

tested on: "LibreOffice" German UI/Locale
           Build-ID: 235ab8a-3802056-4a8fed3-2d66ea8-e241b80
           without additional extensions 
           on german Windows XP professional SP3 32Bit (yes, I'm old fashioned)
Comment 7 Rainer Bielefeld Retired 2012-07-02 05:06:37 UTC
(In reply to comment #6)

Misunderstanding! I know how what effects in Spreadsheet you expect and how to reproduce the problem. 

But I can't imagine any application "in real life" for what that should be necessary or useful. If you can list some applications for what that can be used that might be fixed soon (if it's in ODF definition), if you only know exotic applications that will take some additional years until someone will do a fix.
Comment 8 yandal 2012-07-02 07:55:12 UTC
(In reply to comment #7)

I'm very sorry, but (of course) I'm not able to read anyone's mind
why she/he needs this feature in an application.
What I try to build with it is certainly an exotic application for
a limited number of users.

But nobody knows how many other users (private & commercial) have
been faced with this problem making their work harder or they saw, 
that it's impossible to hit the aim (of her/himself or her/his boss ...)
... and didn't report the bug or found another (much more labour-intensive) 

I can see this feature in many applications, where sheetbuilder
care about flexibility and on-the-fly error-workaround without the
need to handover the actual work to a helpdesk for hours,days or weeks.
In most times, commercial users need a solution, or at least a workaround
for their specific problem a.s.a.p. (own experiences at work).

If this is not enough to push the priority to a level, where the waiting
time for a fix drops under a year, we all have to live with that.
I'm aware that hundreds of other issues may be more important than this
in the subjective view of their reporters.

The only thing I know is, that this bug is known for years now. 
(early OO times)
Discussions about that can be found asking uncle google ...

If you think I'm some impolite, I'm very sorry but my english is not
the best and I personally have the view of a slightly disappointed user.
Comment 9 Markus Mohrhard 2012-07-02 08:49:23 UTC
Please stop useless discussions in bug reports about how important a bug is. I have already put me in CC and if I'll find some time after the 3-6 beta phase I might have a look at it.

If you have more constructive comments please add them but please don't pollute this bug report.
Comment 10 Markus Mohrhard 2012-09-05 18:02:58 UTC
So after some thinking and a talk with Kohei the solution for the cell protection is:

Implement the cell protection for conditional formats after adding code that makes it impossible to define conditional formats in protected sheets.

P.S. Cell protection will only work if the sheet is protected.
Comment 11 Markus Mohrhard 2012-09-06 17:00:50 UTC
I fixed the protected cells through conditional formatting bug.

Sadly git bugzilla integration is not working today, so here are the patches:

Comment 12 ign_christian 2013-06-26 03:42:22 UTC
I'm sorry if I'm bothering. I guess this bug is fixed because after testing with some 'old' files within this bug report & Bug 61525, I found that expected behavior applied.

But after testing with brand new file on LO (Win7 32bit), I found a glitch. 
Simple steps to reproduce:
1. Open new spreadsheet
2. Enter 1 on cell A1, then right-click > Format Cells > Cell Protection to uncheck 'Protected' (to disable protection on A1)
3. Set Conditional Formatting for cell A1:
- Cell value is equal to : 1
- Apply style : Heading (which is Protected by default)
4. Protect Sheet1
5. Modify cell A1 by enter any value

Actual: cell A1 change
Expected: cell A1 should not be changed, because style 'Heading' has protection

That behavior not occur if we change step 3 (modify style 'Heading') above with:
3a. Hit F11 > right-click 'Heading' > uncheck "Protected" under 'Cell Protection' > OK
3b. Then check again "Protected" in style 'Heading' > OK