Apache OpenOffice Community Forum

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

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?

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

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.

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.

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.

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)?

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.

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.

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!

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

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.

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.

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?

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

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.

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.

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.

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.

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.

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!

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.

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/

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

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!

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)

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

Matt

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

LibreOffice 5 does the trick with no macro code.