Bug 129024 - FORMATTING - Preset protected cell background color not displayed in spreadsheet (documentation per Comment 6)
Summary: FORMATTING - Preset protected cell background color not displayed in spreadsh...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Documentation (show other bugs)
Version:
(earliest affected)
6.3.3.2 release
Hardware: x86-64 (AMD64) All
: low minor
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2019-11-25 19:36 UTC by ghaecker@gmail.com
Modified: 2020-06-28 15:43 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:
Regression By:


Attachments
cell color of protected sheet (8.29 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-06-28 14:53 UTC, johnks
Details

Note You need to log in before you can comment on or make changes to this bug.
Description ghaecker@gmail.com 2019-11-25 19:36:46 UTC
Description:
Background color for protected cells in calc do not display the color configured under Preferences (Tools/Options on Win7).  "Allow use of OpenCL" is disabled and cannot be enabled on MacOS 10.14.6 (It can be enabled, but doesn't stick after app restart)

Steps to Reproduce:
1. Select OpenOffice/Preferences/LibreOffice/Application Colors/Protected cells background (Tools/Options/LiebreOffice/Application Colors on Win 7)
2. Set a color for "Protected cells background" 
3. Protect a sheet that has protected cells.
4. The background color does not display for protected cells.

Actual Results:
Configured color for "protected cells background" does not display.

Expected Results:
Display configured background color for protected cells.


Reproducible: Always


User Profile Reset: No


OpenGL enabled: Yes

Additional Info:
Display the configured background color.
Comment 1 Alex Thurgood 2019-11-26 08:53:59 UTC
Confirming with

Version: 6.4.0.0.beta1
Build ID: 4d7e5b0c40ed843384704eca3ce21981d4e98920
Threads CPU : 4; OS : Mac OS X 10.15.1; UI Render : par défaut; VCL: osx; 
Locale : fr-FR (fr_FR.UTF-8); Langue IHM : fr-FR
Calc: threaded

I don't seem to be able to get this to work either, even though a bright colour has been defined in the Preferences, the cells remain the default transparent/clear background colour, unless I assign a specific colour to them manually, which defeats the purpose of setting them at a module level.
Comment 2 Alex Thurgood 2019-11-26 08:54:19 UTC
Did this ever work ?
Comment 3 Alex Thurgood 2019-11-26 08:59:58 UTC
Possible duplicate of bug 36968
Comment 4 ghaecker@gmail.com 2019-11-26 16:29:29 UTC
(In reply to Alex Thurgood from comment #2)
> Did this ever work ?

I don't know. This is the first version I installed on MacOS. My brother confirmed the issue exists with this version on Win7.  My older Win7 version (4.4.2.2) doesn't have the option/preference.
Comment 5 ghaecker@gmail.com 2019-11-29 20:54:01 UTC
Preset background color for protected cells DOES work when Value highlighting is enabled (LibreOffice/Calc/View) and the sheet is protected.  This may be the original intent, as the custom colors for Values, Formulas, and Text are also applied only when Value highlighting is enabled.
Comment 6 Timur 2019-12-02 09:22:09 UTC
Thanks ghaecker for finding the explaination.
That's per Help https://help.libreoffice.org/6.5/en-US/text/scalc/01/03080000.html. 
So that's as expected and bug is literally NotABug.

But looking at this, I noticed that Help doesn't work for Tools - Options - LibreOffice - Application Colors. And I'll convert the bug as documentation for that. That can also go to the existing above-mentioned page.
Comment 7 ghaecker@gmail.com 2019-12-02 15:00:44 UTC
If I may, here's an opinion related to Protected cells background:  a) it would be helpful if this application color was in effect even before the sheet is protected (when Value Highlighting is enabled), and b) protecting the sheet (when Value highlighting is enabled) changes the background color of unprotected cells to white (why should protecting a sheet affect unprotected cells in any way at all?)  In other words, protecting a sheet shouldn't affect any cell background color - it should just - uh - protect the sheet.
Comment 8 Heiko Tietze 2019-12-03 07:53:40 UTC
(In reply to ghaecker@gmail.com from comment #7)
> ...protecting the sheet (when Value highlighting is enabled) changes the 
>  background color of unprotected cells to white

I cannot follow. Please provide step by step like 

1. Insert a number in A1
2. Insert a formula in A2
3. Enable value highlighting (makes font color in a1 blue and a2 green)
4. Enable sheet protection (changes the background into gray but should...?)
Comment 9 ghaecker@gmail.com 2019-12-03 16:29:26 UTC
(In reply to Heiko Tietze from comment #8)
> (In reply to ghaecker@gmail.com from comment #7)
> > ...protecting the sheet (when Value highlighting is enabled) changes the 
> >  background color of unprotected cells to white
> 
> I cannot follow. Please provide step by step like 
> 
> 1. Insert a number in A1
> 2. Insert a formula in A2
> 3. Enable value highlighting (makes font color in a1 blue and a2 green)
> 4. Enable sheet protection (changes the background into gray but should...?)

Okay, I finally understand how value highlighting works. Since this issue has been changed to Documentation (NotABug), I'll provide steps to illustrate how it works and where the documentation is inadequate. I'll also offer suggestions on how to make value highlighting behavior more consistent and intuitive than it is now.

Application Color abbreviations
  docFG - General/Font color
  docBG - General/Document background
  vhTextFG - Spreadsheet/Text
  vhValueFG - Spreadsheet/Values
  vhFormulaFG - Spreadsheet/Formulas
  vhProtectedBG - Spreadsheet/Protected cells background

As it is now:

1. Select al cells and deselect the protected attribute
2. Insert text in A1
3. Insert a number in B1
4. Insert a formula in C1
5. In D1 set the protected attribute
6. Select the range, A1:D1, and set a user-defined background color like Light Yellow 3, which I'll just call userBG. All 4 cells show userBG and docFG, as expected.
7. Enable value highlighting - As expected, A1, B1, and C1 change foreground color from docFG to vhTextFG, vhValueFG, and vhFormulaFG, respectively. Oddly, however, none of the background colors are affected - all 4 cells still show userBG
8. Protect the sheet - As expected, foreground colors remain unchanged and correspond to the value highlighting colors. However, all 4 cells background colors are changed. D1 background changes from userBG to vhProtectedBG. Unexpected (and undocumented) is the background change in unprotected cells, A1:C1, from userBG to docBG.  

Current documentation for value highlighting states and value highlighting overrides user-defined colors.  That's fine, but the application doesn't do it consistently. When value highlighting is enabled, the 3 configurable foreground colors (vhTextFG, vhValueFG, and vhFormulaFG) are applied whether the sheet is protected or not.  The 2 configurable background colors for value highlighting are only applied when the sheet is protected. Moreover, one of the configurable background colors (docBG) is a global color, affecting all modules.


Suggestion for more consistent/intuitive behavior:

1. When value highlighting is enabled, enforce all 5 related colors, whether or not the sheet is protected.  The sheet doesn't have to be protected in order to know if a cell has the protected attribute. Sheet protection is about restricting access, not about display/formatting.

2. Add another configurable value highlighting color, "Unprotected cells background", so that it can be configured separately from the global docBG setting.

In addition to providing more consistent behavior, these changes would also simply documenting the feature. It would just be a simple on/off toggle that overrides user-defined formatting.
Comment 10 Heiko Tietze 2019-12-04 07:40:01 UTC
(In reply to ghaecker@gmail.com from comment #9)
> I'll provide steps to illustrate how it works and where the documentation 
> is inadequate. 

Cool, thanks a lot. The next step in open source is to add/implement this yourself. Resistance is futile :-)
Comment 11 johnks 2020-06-28 14:53:18 UTC
Created attachment 162477 [details]
cell color of protected sheet
Comment 12 johnks 2020-06-28 14:59:30 UTC
Comment on attachment 162477 [details]
cell color of protected sheet

i have attached a simple sheet showing this error on my calc 

password of the file is "asd" without the quotes 

the two sheets are identical but the second sheet is protected. visually sheet protection should NOT make a difference but it is on my instance and i remember this as far as i can recall.


Version: 6.4.2.2
Build ID: 4e471d8c02c9c90f512f7f9ead8875b57fcb1ec3
CPU threads: 4; OS: Linux 4.15; UI render: default; VCL: kf5; 
Locale: en-IN (en_IN.UTF-8); UI-Language: en-US
Calc: threaded
Comment 13 johnks 2020-06-28 15:43:04 UTC
to add to this, if a protected sheet has a color prior to setting sheet protection, that color is shown even AFTER protection is applied