[Solved] Checkbox as an OpenOffice Calc cell

[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 27 times

thanks again for all your help

[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.

[TheMotorcyleBoy]

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.

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.

[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.

[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.

[Lupp]

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

aoo93878CellAsButtonAndProtection_LibreOffice_Villeroy_reworked.ods

(12.27 KiB) Downloaded 17 times

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.