[Solved] Checkbox as an OpenOffice Calc cell

Discuss the spreadsheet application
TheMotorcyleBoy
Posts: 21
Joined: Sun Jun 10, 2018 4:30 pm

Re: Checkbox as an OpenOffice Calc cell

Post by TheMotorcyleBoy »

Thanks everyone,

Yes I figured it all out (I'd already encountered the issue with sheet/cell protection) eventually.

I've uploaded the "almost completed" sheet. You can see that if the state of the chkbox (@B102) is toggled, then the protected state of the input cell for Annual lease costs is toggled too.

When the sheet is being used all the numeric entry fields which are black in font colour will be protected (they are calculated values). The red cells are required entry by the user.....and by default chkbox B102 will be disabled. Hence the user (me or my wife) enables this when they are analysing a high street retailer - i.e. they have hidden lease costs which are capitalised and subtracted from operational pro, and a bunch more parameters are then produced for valuation.
g.ods
Annual lease costs is protected is the checkbox is disabled
(50.78 KiB) Downloaded 103 times
thanks again for all your help :super:
LibreOffice 4.2.6.2 on Fedora Core 20
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Checkbox as an OpenOffice Calc cell

Post by Lupp »

@Villeroy:

-1- Being able to remove the protection from a cell based on CF with the help of a checkbox while the sheet remains protected I would assume a bug. Do you think it's a feature? In fact it does not work if the cell controllimg the CF not is the linked cell of a FormControl.
How did you find that to work? Is there any information?

-2- The usage of a cell's area as a surrogate for a PushButton is a kind of a joke, of course. There are issues with HYPERLINK(), however. We may use an ordinary linked text instead. The syntax for the passing of parameters needs improvement and simplification. Easy.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
TheMotorcyleBoy
Posts: 21
Joined: Sun Jun 10, 2018 4:30 pm

Re: Checkbox as an OpenOffice Calc cell

Post by TheMotorcyleBoy »

Lupp wrote:Being able to remove the protection from a cell based on CF with the help of a checkbox while the sheet remains protected I would assume a bug. Do you think it's a feature?
IMO it's a feature.
Lupp wrote: In fact it does not work if the cell controllimg the CF not is the linked cell of a FormControl.
How did you find that to work? Is there any information?
I can't comment on attempted compatibility to MS. But what you iterate above, I experienced too. The user of these tools (e.g. me using LO) is right to expect the simplest and cleanest solution possible. There should be no requirement for a special linked and superflous cell. The user should be able to link the enabled state of the chkbox directly to the CF of the cell they wish to affect.
LibreOffice 4.2.6.2 on Fedora Core 20
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Checkbox as an OpenOffice Calc cell

Post by Villeroy »

IMHO, the LO devs changed this due to popular vote. Sheet protection is not a real protection against evil hackers. For instance I could easily remove the sheet protection from Lupp's document without knowing the set password.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
TheMotorcyleBoy
Posts: 21
Joined: Sun Jun 10, 2018 4:30 pm

Re: Checkbox as an OpenOffice Calc cell

Post by TheMotorcyleBoy »

It's pretty obvious that cell protection was just added to prevent "users" as opposed to "developers" inadvertently making an unintentional (and possibly time-consuming in recovery) editing - which is exactly the reason I sought to protect the "calculated" value cells in my corporate analysis sheet. Not as an anti-hacker tool.
LibreOffice 4.2.6.2 on Fedora Core 20
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Checkbox as an OpenOffice Calc cell

Post by Lupp »

Villeroy wrote:For instance I could easily remove the sheet protection from Lupp's document without knowing the set password.
I knew you could anyway, but I also hinted the way to look into the contained Basic script.

I don't know the history of the supposed feature. However I understand its usefulness. "TheMotorcycleBoy" is right on the other hand with his idea concerning a way to access the state of the CheckBox directly from the CF editor. On the third hand, already to word this as a feature request might be complicated (if you not can just refer to "how Excel does") and the implementation would require lots of changes in the CF code - and in the concerned FormControl objects. These objects are not primarily made for spreadsheets, and currently it's even difficult to find out the sheet in which a Control is placed using the API. A Calc function for access to any specific Control would require a unique naming e.g. for referencing it ...
No way!

And on the fourth hand finally I still do not understand for what reasons the state of a cell being linked to a CheckBox is allowed to change cell-protection for any range by the overlaid cellstyle, while the same cell is not allowed to do so if not linked.

Edit1: I could not yet find a related enhancement. Neither a (fixed) request in bugs.documentfoundation.org nor a mention in release notes.

Edit2: See also
[/strike]
Edit3: I tried to replicate the behaviour originally claimed by me without success. Feeling in danger to go crazy about this topic, I will retire from it.
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply