I have a calc sheet with hundreds of form control checkboxes on it that will have more checkboxes than what’s already on it added to it in the future. I’d like to have a button to change all the checkbox values on the same sheet the button is on, not the entire document, to FALSE so they’re unchecked quickly and all at once.
Learning Libre Basic is beyond what I’ve been able to grasp. Would someone please be kind enough to share Basic macro code that would do this?
We already solved this problem many years ago - have you forgotten? Or don’t know how to apply that code to the current task?
Hallo
just for fun in python:
def reset_checkboxes( event ):
"""
link it to Event: »Mouse-button-pressed« of Command-Button
"""
controls = event.Source.Context.Controls
for control in controls:
model = control.Model
if model.supportsService("com.sun.star.form.component.CheckBox"):
model.State = False
I do remember that, appreciate it, and I’m using it on this same document. Doesn’t that require the addresses for the targeted cells the checkboxes are in placed the Additional Info field of the control properties?
I’ve attempted putting all of the cell ranges for each checkbox in that field for the button I’m trying to make with it. The Additional Info field doesn’t seem to want to accept all of them, I assume because there’s too many. They don’t all fit in that text field. Maybe I did that wrong. I used a comma to separate them. Must I use a different character other than comma?
I repeated asking this because while that works great for what I have it applied to it doesn’t seem to scan the current sheet and do it to all of the checkboxes regardless of what cell they’re in.
Thanks! That seems to be what I’m looking for. Don’t we have to do a things with options and preferences to use Python scripts in Calc? And would a user of the document have to do anything like we have to do enabling JRE to run macros?
I’m trying to make this document easy for anyone to use. I’d prefer it not needing additional setup steps. I’ve looked in to running Python scripts in calc years ago and remember I threw that idea in the ‘nay’ bin because it was a task to get Python working at that time. I’ll look into it again.
Edit: I looked into it and it looks to be quite easier with APSO now. I’ll give this a try. Thank you very much!
I’m also interested in seeing what JonSun replies to my reply. Maybe what he wrote for me years ago does this and I’m just not setting it up right.
Just save the text under /Scripts/python/Some_Module_Name.py
Disable Java if you like
I don’t have a Scripts folder in the installation’s root directory.
Thank you very much! Seems to work perfectly. How do I include this python script with the document? Must I save it somewhere else other than the shared/Scripts/Python folder?
Hmm.
The python script worked when I tested it. I installed APSO and moved it to include it with the document and now it doesn’t work. I’m getting this error:
com.sun.star.uno.RuntimeException: Error during invoking function reset_checkboxes in module vnd.sun.star.tdoc:/1/Scripts/python/Reset_CheckBoxes.py (<class ‘TypeError’>: reset_checkboxes() missing 1 required positional argument: ‘event’
File “C:\Program Files (x86)\LibreOffice\program\pythonscript.py”, line 916, in invoke
ret = self.func( *args )
)
Doesn’t work.
I see that you have it in Approved Actions and it’s included in the document. When I remove the macro from Approved Actions and apply it to Mouse Button Pressed it still does not work.
The .py code is there if I go into APSO and select Edit. Hopefully someone fills me in on what I have to do so it works embedded in the document.
you should not try to test it via somewhere from →Makro→execute because it needs to be triggered by the event of the button
ps: it works with any of: Approved Actions , MouseButton [pressed|released]
Rule of thumb for editing and testing event-driven python:
Edit and test on NOT embedded Code… embedding and rebinding to some Event should be the very last step in the chain
Here’s what I did:
Step 1. Copied the code you shared and saved it in a text file (notepad++) to:
Step 2: I opened a new, blank, Calc document. I made some check boxes and a push button then applied the script to Mouse Button Pressed. I then checked some checkboxes (not all) and pressed the button to test it
it worked so I then…
Step 3: Opened the project Calc file and added a new sheet and did the above step 2 on a new sheet. It worked.
Step 4: I didn’t yet have APSO installed so I downloaded and installed it, restarted Libre Calc as required and tested it again several times on the new sheet in the project file.
Step 5: Used APSO to move the Reset_CheckBoxes.py to and embed it in the document. I think this is where I made the mistake. I didn’t test it after moving and embedding it. I don’t see how that makes a difference but I guess it does.
I believe this is where I missed or didn’t do something correctly.
Step 6: Since it worked great during testing I applied it to the push button in the project file I want it for. It did not and still does not work.
Okay, if implementing a Python script into a spreadsheet is such a hassle, then maybe it’s really worth going back to the familiar things - they may not be fancy and they may be slow and clunky, but they are familiar and they work. Fortunately, the script that @karolus kindly suggested is translated into BASIC almost word for word:
Sub ResetAllCheckboxesOnSheet(oEvent As Variant)
Dim oControl As Variant, oModel As Variant
For Each oControl In oEvent.Source.getContext().getControls()
oModel = oControl.getModel()
If oModel.supportsService("com.sun.star.form.component.CheckBox") Then oModel.State = False
Next oControl
End Sub
I doubt this works in the »original Project?« because:
@Bort: your description is insufficient… what happens? is there an Errormessage? which one?…post it by copy&paste! … attach the original Projectfile without personal Data!
“It didn’t work” isn’t helpful because it tells us what did not happen. Please never use that phrase in a post. We need to know exactly what actions you took, what did happen including any error messages, and what you expected to happen.
Nothing happens when I apply either of the very highly appreciated scripts shared for me when I press the button after applying one of them. I of course don’t try applying both at the same time. The button animates and nothing happens.
When I run @JohnSUN’s Basic script from Tools > Macros > Run script I get this error:
The project file has over 600 people’s personal information in it. That has to do with what these checkboxes are for. Therefore I’m reluctant to share the project file publicly. I’d have to tear the document apart which then disables just about everything it does. I didn’t share the file originally for that reason.
I’ll make up a test file that’s extremely similar and share it later tonight or tomorrow. I’m using time I don’t have to respond to your kind attempts to help me with this now.
Thank you all very much for all the responses and attempts at helping with this!
Yes, @karolus warned about exactly this - the macro is designed to be called by an event from a button
If you need it to be able to be called both from the button and from the Run menu, then the script code will be much longer
Sub ResetAllCheckboxesOnSheet(Optional oEvent As Variant)
Dim oDrawPage As Variant, i As Long
Dim oControl As Variant, oModel As Variant
If IsMissing(oEvent) Then
oDrawPage = ThisComponent.getCurrentController().getActiveSheet().getDrawPage()
For i = 0 To oDrawPage.getCount()-1
oControl = oDrawPage.getByIndex(i)
If oControl.supportsService("com.sun.star.drawing.ControlShape") Then
oModel = oControl.getControl()
If oModel.supportsService("com.sun.star.form.component.CheckBox") Then oModel.State = False
EndIf
Next i
Else
For Each oControl In oEvent.Source.getContext().getControls()
oModel = oControl.getModel()
If oModel.supportsService("com.sun.star.form.component.CheckBox") Then oModel.State = False
Next oControl
EndIf
End Sub
Did @Villeroy write to you? Or was he writing to someone else? It doesn’t matter, he wrote this many, many times: attempts to turn a spreadsheet into a database were made more than once and sooner or later failed - the project became unmanageable, the accumulated data was lost, the developer went crazy and became depressed… Don’t do that - use a database: it is databases that are invented, developed and intended for the accumulation, storage, and processing of data. And spreadsheets are a means for calculations, don’t get confused!
PS. By the way, my nickname has 7 letters - the first four are the normal spelling of the name John, the last three can mean Stanford University Network (or if you don’t know what that is, then just let it mean the Sun)
Apologies I misspelled your display name, @JohnSUN !
That takes a while to clear them all but it works! Thanks!
Yes @Villeroy did reply here and I’m awaiting their response, I did try what they suggested. The python script seems to work more efficiently,
The Basic script you shared is much appreciated and it does work it’s just, as I think you shared it might, John, works slower and less efficiently. That’s ok, it works! I just want something that works.
I tried using Base creating a database to use for this project. I couldn’t get it to do anything at all with the calc document. I’m familiar with MySQL but Base is something totally new to me. So, yes, not getting a database working for this project is due to my ignorance of how Base works with Calc documents.
This project is an ongoing thing that’s been working and being used for over 10 years. I’m reluctant to change it drastically or start all over from the beginning again to produce the same thing using a database just to use a database. It’s working the way it is and everyone that uses it understands it.