[Solved] Checkbox as an OpenOffice Calc cell

Discuss the spreadsheet application

Re: Checkbox as an OpenOffice Calc cell

Postby TheMotorcyleBoy » Wed Jun 13, 2018 7:53 pm

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 13 times


thanks again for all your help :super:
LibreOffice 4.2.6.2 on Fedora Core 20
TheMotorcyleBoy
 
Posts: 21
Joined: Sun Jun 10, 2018 4:30 pm

Re: Checkbox as an OpenOffice Calc cell

Postby Lupp » Wed Jun 13, 2018 11:41 pm

@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 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 1914
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Checkbox as an OpenOffice Calc cell

Postby TheMotorcyleBoy » Thu Jun 14, 2018 7:03 am

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
TheMotorcyleBoy
 
Posts: 21
Joined: Sun Jun 10, 2018 4:30 pm

Re: Checkbox as an OpenOffice Calc cell

Postby Villeroy » Thu Jun 14, 2018 10:28 am

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 25579
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Checkbox as an OpenOffice Calc cell

Postby TheMotorcyleBoy » Thu Jun 14, 2018 12:51 pm

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
TheMotorcyleBoy
 
Posts: 21
Joined: Sun Jun 10, 2018 4:30 pm

Re: Checkbox as an OpenOffice Calc cell

Postby Lupp » Thu Jun 14, 2018 1:03 pm

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

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 6.1 and older versions, PortableOpenOffice 4.1.5 and older, StarOffice 5.2
---
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 1914
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Previous

Return to Calc

Who is online

Users browsing this forum: No registered users and 25 guests