[Solved] Checkbox as an OpenOffice Calc cell

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

[Solved] Checkbox as an OpenOffice Calc cell

Post by TheMotorcyleBoy »

Hi everyone,

Is there a way that I can make a cell behave like a checkbox? Such that when the box is in the true state, then a collection of cells below will be valid candidates for data entry.

I'm a programmer by profession, so don't overally worried about writing some code as long as someone can also tell where to write the code, and how to associate it with the sheet.

many thanks
Matt
Last edited by TheMotorcyleBoy on Fri Jun 15, 2018 2:05 pm, edited 1 time in total.
LibreOffice 4.2.6.2 on Fedora Core 20
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Checkbox as an OpenOffice Calc cell

Post by Zizi64 »

I'm a programmer by profession, so don't overally worried about writing some code as long as someone can also tell where to write the code, and how to associate it with the sheet.
You need study the API (Application Programming Interface) functions of the Apache OpenOffice or the LibreOffice first for the macro programming. Then you will able write some code for this task by usage one of the supported programming languages: StarBasic + built-in IDE or BeanShell or JavaScript or Python.

Download and read please Andrew Pitonyak's free macro books.

uch that when the box is in the true state, then a collection of cells below will be valid candidates for data entry.
Do you want to set/reset the write protection property of some input cells based on the state of the checkbox?
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Checkbox as an OpenOffice Calc cell

Post by UnklDonald418 »

As Zizi64 noted the difficulty isn't so much with programming as it is with the API used to access OpenOffice.
There are a couple of books available online
“OpenOffice.org Macros Explained” by Andrew Pitonyak
http://www.pitonyak.org/oo.php
uses the Star Basic language that was developed with OpenOffice. There is a built in IDE for debugging Basic code.

If you prefer “Java LibreOffice Programming” by Dr. Andrew Davison
http://fivedots.coe.psu.ac.th/~ad/jlop/

Since OpenOffice is object oriented you will need an object inspection tool. The MRI tool is a good option.
[Tutorial] Introduction into object inspection with MRI
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Checkbox as an OpenOffice Calc cell

Post by Zizi64 »

OpenOffice 4.2.6.2 on Fedora Core 20
I suppose that is LibreOffice but not Apache OpenOffice. There is not AOO 4.2.x (stable) version today.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
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 »

bool_cell.ods
(14.98 KiB) Downloaded 456 times
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 »

Zizi64 wrote:
OpenOffice 4.2.6.2 on Fedora Core 20
I suppose that is LibreOffice but not Apache OpenOffice. There is not AOO 4.2.x (stable) version today.
Sorry it is LibreOffice.

The signature I enrolled on this site with was copied from the help/about.

Am I posting to the wrong forum? I took advice from another place and was directed to here.
LibreOffice 4.2.6.2 on Fedora Core 20
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Checkbox as an OpenOffice Calc cell

Post by Zizi64 »

Am I posting to the wrong forum?
Not, you are on the best place. It is a common Forum for AOO and LO. But there are differences between the two office suites, therefore we must know what you are using really. Then (maybe) we can give you answer to a version-specific question.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
TheMotorcyleBoy
Posts: 21
Joined: Sun Jun 10, 2018 4:30 pm

Re: Checkbox as an OpenOffice Calc cell

Post by TheMotorcyleBoy »

Ok, sorry, I've just updated my profile such that my signature says "LibreOffice".

So returning to my original question.....

Firstly does LibreOffice 4.2.6.2 calc, support a feature where a cell can effectively change it's internal state based on whether the user clicks it with a mouse (this being the most orthodox mechanism to toggle a check box)?
LibreOffice 4.2.6.2 on Fedora Core 20
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Checkbox as an OpenOffice Calc cell

Post by Zizi64 »

does LibreOffice 4.2.6.2 calc, support a feature where a cell can effectively change it's internal state based on whether the user clicks it with a mouse (this being the most orthodox mechanism to toggle a check box)?
Yes. They named: Form Control elements. Switch on the toolbar: View - Toolbars - Form Controls.

Switch it to Edit mode by clicking on the second icon of the toolbar.
Put a checkbux on th sheet, resize it, and the right click on it.
Now you can adjust the properties of the checkbox include the control properties, like the assigned cell.
Switch off the edit mode, ant the check box will work.
Last edited by Zizi64 on Mon Jun 11, 2018 6:46 pm, edited 1 time in total.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
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 »

More detailed version with:
1) Data>Validation which turns the cell itself into a form control similar to a list box. Source range is H1:H2
2) Form contol check box
3) Form controls option buttons
4) Form control list box. Source range is H1:H2.

The form controls are designed to work with database records. Their possibilities in Calc are limited.
Validation can be copied across many cells.
Attachments
bool_cell2.ods
(15.72 KiB) Downloaded 279 times
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 »

Thanks for those last two posts people. I'll have a play with some of those ideas when I get a chance and post back telling you all how I got on, or more questions accordingly!
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

Post by TheMotorcyleBoy »

Zizi64 wrote:
does LibreOffice 4.2.6.2 calc, support a feature where a cell can effectively change it's internal state based on whether the user clicks it with a mouse (this being the most orthodox mechanism to toggle a check box)?
Yes. They named: Form Control elements. Switch on the toolbar: View - Toolbars - Form Controls.

Switch it to Edit mode by clicking on the second icon of the toolbar.
Put a checkbux on th sheet, resize it, and the right click on it.
Now you can adjust the properties of the checkbox include the control properties, like the assigned cell.
Switch off the edit mode, ant the check box will work.
Thank you very much Zizi,

So I have now placed check box on my sheet. Please find attached a small screenshot of my progress, so that I can ask a question. I right-clicked the check box and the dialog (see my graphic) appeared titled "Properties", and below is the check box, and underneath that are some cells (c103, c104, c105, c106 and c107) which I would like to unprotect if the checkbox is checked, otherwise they should be disabled from data entry.
Graphic of my check button on sheet design so far.
Graphic of my check button on sheet design so far.
In such a case, must I associate some code with the "Mouse button released" event? Would I do that by clicking the corresponding [...] button on the "Properties" dialog?

thanks again, Matt
LibreOffice 4.2.6.2 on Fedora Core 20
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Checkbox as an OpenOffice Calc cell

Post by UnklDonald418 »

must I associate some code with the "Mouse button released" event? Would I do that by clicking the corresponding [...] button on the "Properties" dialog?
Yes, click on the ellipsis […] to the right of an Event to open the Assign Action dialog. There you can link to some Macro code that that has already been entered. The macro will be executed whenever that particular Event is triggered by the Check Box control.
I would try the "Item Status changed" Event which should execute the macro whenever the Check Box is toggled.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Checkbox as an OpenOffice Calc cell

Post by Zizi64 »

Just a comment:

You sample file is an .xls file. NEVER use the foreign, obsolete, never standardized file formats for your important documents with the AOO and LO - because there is not (never was and never will be) 100% compatibility between the different fileformats. You will lost format properties and/ or functionalities with the foreign file formats.
Always use the native, International Standard ODF fileformats (.ods for the speadsheets). When you want write some macros (and maybe you want store them in the document) it is more important circumstance.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
TheMotorcyleBoy
Posts: 21
Joined: Sun Jun 10, 2018 4:30 pm

Re: Checkbox as an OpenOffice Calc cell

Post by TheMotorcyleBoy »

UnklDonald418 wrote:
must I associate some code with the "Mouse button released" event? Would I do that by clicking the corresponding [...] button on the "Properties" dialog?
Yes, click on the ellipsis […] to the right of an Event to open the Assign Action dialog. There you can link to some Macro code that that has already been entered. The macro will be executed whenever that particular Event is triggered by the Check Box control.
I would try the "Item Status changed" Event which should execute the macro whenever the Check Box is toggled.
Thank you very much.

In order to set the protected attribute of, for example, cell 103, can I write
c103.protected = True
or should I make c103 the "linked cell" for this control and say
linked_cell.protected = True
is that how it works?
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

Post by TheMotorcyleBoy »

Zizi64 wrote:You sample file is an .xls file. NEVER use the foreign, obsolete, never standardized file formats for your important documents with the AOO and LO - because there is not (never was and never will be) 100% compatibility between the different fileformats. You will lost format properties and/ or functionalities with the foreign file formats.
Always use the native, International Standard ODF fileformats (.ods for the speadsheets). When you want write some macros (and maybe you want store them in the document) it is more important circumstance.
Are you recommending that I should now, do "Save As" into .ods since I'm now exploiting features which might not port to a future version of LO?

Matt
LibreOffice 4.2.6.2 on Fedora Core 20
User avatar
robleyd
Moderator
Posts: 5055
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Checkbox as an OpenOffice Calc cell

Post by robleyd »

Features which may not be available in foreign - as in non ODF - formats like MS proprietary formats. Unless you have turned the message off, you should get a warning that saving in a foreign file format may result in possible loss of format or features.
Cheers
David
OS - Slackware 15 64 bit
Apache OpenOffice 4.1.15
LibreOffice 24.2.1.2; SlackBuild for 24.2.1 by Eric Hameleers
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Checkbox as an OpenOffice Calc cell

Post by Zizi64 »

A comment again:

The cell protection works when the Sheet protection is switched on. You must prepare your sheet, if you want enable to edit the other cells.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Checkbox as an OpenOffice Calc cell

Post by Zizi64 »

Are you recommending that I should now, do "Save As" into .ods since I'm now exploiting features which might not port to a future version of LO?
The MS spreadsheet software versions have a lot of different, incompatible binary .xls file formats:
Excel 5, Excel 95, Excel 97/2000/XP, Excel 2003...


The ODF (.ods) was developed for the long time compatibility.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
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 »

This is not an alternative editor for MS documents. It can import and export MS documents pretty well but with potential losses here and there. For improved compatibility with those file formats you should install LibreOffice instead of OpenOffice but even with LO you are recommended to work with the native ODF document formats and export to doc/xls/ppt in very rare cases. For MS documents you have to use MS software.
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 »

FWIW, I never really plan on using the MS equivalents.

But up until now, I have always saved any LO/OO documents etc. into the MS formats (i.e. 97/20xx/whatever) just really to have a common baseline format.

I do appreciate that certain esoteric features won't work too well across editors.

And now I'm about to embark onto macros, then clearly the native ODS format, seems the only sensible approach.
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

Post by TheMotorcyleBoy »

Villeroy wrote:
bool_cell.ods
Thanks Villeroy,

I just had a quick look at this .ods - did you actually put any macro code in it? If so could you describe how I can navigate to that code?

thanks!
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

Post by TheMotorcyleBoy »

I think I have figured out what I need to do:
1. enable the sheet protection stuff
2. place checkbox on sheet and link a cell
3. for the cells I wish to protect or not, I'll create a custom style.
4. then for those cells, apply the conditional format to change their style to have cell protection depending on the boolean state of the above chkbox or something.

:D
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

Post by TheMotorcyleBoy »

My last post does not help me. As I'm sure you people "conditional format" for a cell seems to be restricted to mainly comparisions of the current cells value. Not reference to OTHER cells in the sheet. As far as I can see....

So I'm now trying to learn how to code a macro, to control another cell based of an event raised on my chkbox.

Have just read a bit of

https://wiki.documentfoundation.org/ima ... Macros.pdf

but this kind of thing really leaves me in the dark:
document=ThisComponent.CurrentController.Frame
dispatcher=createUnoService("com.sun.star.frame.DispatchHelper")
dispatcher.executeDispatch(document,".uno:GoToNextWord"....
Surely there must be a more intuitive way to reference a given cell, and then discover properties etc. etc?

EDIT: Just found this
http://www.debugpoint.com/2015/02/delet ... ing-macro/


http://www.debugpoint.com/2015/02/delet ... ing-macro/
LibreOffice 4.2.6.2 on Fedora Core 20
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Checkbox as an OpenOffice Calc cell

Post by Zizi64 »

As I'm sure you people "conditional format" for a cell seems to be restricted to mainly comparisions of the current cells value. Not reference to OTHER cells in the sheet. As far as I can see....
You can reference other cells too (the attached picture was created in my LibreOffice 5.4.7 - the CF function works differently in AOO):
CF.png
Checbox_Conditional format.ods
(9.96 KiB) Downloaded 181 times
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
TheMotorcyleBoy
Posts: 21
Joined: Sun Jun 10, 2018 4:30 pm

Re: Checkbox as an OpenOffice Calc cell

Post by TheMotorcyleBoy »

Zizi64 wrote:
As I'm sure you people "conditional format" for a cell seems to be restricted to mainly comparisions of the current cells value. Not reference to OTHER cells in the sheet. As far as I can see....
You can reference other cells too (the attached picture was created in my LibreOffice 5.4.7 - the CF function works differently in AOO):
CF.png
Checbox_Conditional format.ods
Thank you,
This is quite clear now, I can see that the evaluation of a formula, distinct from comparision of own cell value, means that the format condition can be made contingent on another cell's state.

It just seems a shame that the designers of LO didn't arrange that for the cell designation of the chkbox itself to be used, instead of the linked cell, in the formula. That way, the ugliness of the appearance of the linked cell (plus the resulting issue of trying to protect the linked cell) could be avoided. I did briefly try this.....in Zizi's example, this meant changing A4 in the condition formula to B4....but this does not work.....the linked cell is required :(

However, thank you very much for explaining this to me!

:bravo:
LibreOffice 4.2.6.2 on Fedora Core 20
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Checkbox as an OpenOffice Calc cell

Post by Zizi64 »

It just seems a shame that the designers of LO didn't arrange that for the cell designation of the chkbox itself to be used, instead of the linked cell, in the formula. That way, the ugliness of the appearance of the linked cell (plus the resulting issue of trying to protect the linked cell) could be avoided. I did briefly try this.....in Zizi's example, this meant changing A4 in the condition formula to B4....but this does not work.....the linked cell is required :(
That is not a shame. It is a conceptional solution, "inherited" from the Excel. You can write your own macro functions for handle the Form control elements better.

And you can use the Named cells (Named ranges) function in your formulas: You can name the linked cell as "CheckBox1" for example,
and you can "hide" the linked cell: just put it into an another (helper) sheet, or format it as invisible, or put it into the cell containing the checkbox (put it "behind" the checkbox).

Conclusion: Study and use the EXISTING functions and features, or be a developer, and please develop new ones for us. (We are users like you in this Forum)
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
TheMotorcyleBoy
Posts: 21
Joined: Sun Jun 10, 2018 4:30 pm

Re: Checkbox as an OpenOffice Calc cell

Post by TheMotorcyleBoy »

Zizi64 wrote: And you can use the Named cells (Named ranges) function in your formulas: You can name the linked cell as "CheckBox1" for example,
and you can "hide" the linked cell: just put it into an another (helper) sheet, or format it as invisible, or put it into the cell containing the checkbox (put it "behind" the checkbox).
Yes, that's the kind of workaround I'd imagine.

25 or so years ago, I did much UI work, with Visual Basic....and found those aspects a little tedious.
Zizi64 wrote:Conclusion: Study and use the EXISTING functions and features, or be a developer, and please develop new ones for us. (We are users like you in this Forum)
TBH I am a developer - I write debuggers and disassemblers for mobile phone chips. Usually in C++. Do a lot of python too - mainly for construction of test infrastructures. UI work is something I very rarely do nowadays.

The spreadsheet around which I based my query, is due to personal investment strategy I'm currently researching.

But if I do end up writing a nice LO macro, I'll certainly share it here :D

Matt
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 »

As far as I know:
You cannot remove protection from a protected cell (neither directly nor by running a script) without removing protection from the sheet in advance.
To remove the protection from a sheet using a script the script needs to know the password (if not empty) - which then will be an unencrypted part of an accessible text.

Editing:
I was in a playful mood and made a demo taking the original request partly literally, partly distorted: "Calc cell as a PushButton".

The Basic script contained in the attached file was written from scratch and most of the paths through the code are not tested.

As the probably useful aspect of the playful approach you may see a (reduced) example of how to pass parameters to scripts when called via a hyperlink. In a very similar way you can pass parameters using the .Tag property - if available - of a FormControl ('Additional information' in LibreOffice) or a different text property otherwise unused..
Attachments
aoo93878CellAsButtonAndProtection_1.ods
(12.8 KiB) Downloaded 166 times
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
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 »

LibreOffice 5 does the trick with no macro code.
Attachments
aoo93878CellAsButtonAndProtection_LibreOffice.ods
(11.15 KiB) Downloaded 221 times
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
Post Reply