How do I get a checkbox or button to check and uncheck checkboxes in a column range when the range is cells F3 to F19?
The macro that will do the job looks like this:
Sub onBtnGroup(oEvent As Variant)
Dim sRngAddress As String
Dim oSheet As Variant
Dim oRange As Variant
Dim oData As Variant
Dim i As Long, j As Long
sRngAddress = oEvent.Source.getModel().Tag
oSheet = ThisComponent.getCurrentController().getActiveSheet()
oRange = oSheet.getCellRangeByName(sRngAddress)
oData = oRange.getDataArray()
If oData(0)(0) = 0 Then
For i = LBound(oData) To UBound(oData)
For j = LBound(oData(i)) To UBound(oData(i))
oData(i)(j) = 1
Next j
Next i
Else
For i = LBound(oData) To UBound(oData)
For j = LBound(oData(i)) To UBound(oData(i))
oData(i)(j) = 0
Next j
Next i
EndIf
oRange.setDataArray(oData)
End Sub
Place the button on the sheet. In the properties of the button, turn off “Take focus on click” and enter in the “Additional information” field the address of the range of cells to which the values of your checkboxes are linked. In the Events tab, specify a macro to handle the event Execute action.
Disable design mode and test how the button works. Return to design mode again, copy the button and paste it next to each group of checkboxes, changing the address of the range in the Additional Information field.
1 Like
Works! Thank you!
Write a macro to achieve this task.
Please upload your ODF type sample file here.
My approach is a littlebit different than the approach JohnSUN’s solution.
In my solution the position of the checkboxes are irrelevant, the macro examine the TAG/Additional information of the checkboxes. i filled-in this property on the Sheet1: The value for the master checkboxes are M1, M2, M3, and tho other checkboxes has 1 value in the forst group, 2 in the second group, and 3 in the third group.
The macro is assigned to all of the Master checkboxes (on the Sheet1 only).
Example file added. I placed text in the sheets to share what I’m trying to get it to do.
I wasn’t able to save as an ODF. I don’t have that option.
ODF: Open Document Formats. One of this type is the .ods.
Thanks. I have 100% no clue how to write or edit macros. Even when I try to edit a simple thing like the sub name, I assume subs are the module name, it errors. Calc throws a fit and I get carpet bombed with error prompts. I have no idea how to transfer this to everywhere it’s needed.
I’ll leave this open in case you’ll be kind enough to explain it, Zizi64. I’m more than happy as well as grateful for instructions and learning. I’ve been trying to learn scripting like this for decades but I just can’t make any sense of it. Nothing about this or C# or java scripting is obvious, easy to understand, plain text like the languages I do know.
The programming language is the StarBasic. it is a very simple programming language with a few embedded commands only.
But!
There is the API of the Libreoffice. (API: Application Programming Interface). The API has thousands of functions and subroutines for control the applications and the documents.
I suggest you to install and use one of the excellent object inspection tools: the MRI or the XrayTool.
Then you will able to examine and list the properties and methods of the programming objects. You can see the REM-ed commads for the calling the XrayTool installed on my computer.
How to edit the macros:
Tools - Macros - Edit macros
Browse the Module1 of my attached document, then you will see the macro code. The macro code what are embedded into a document, will work in the document only. If you copy the code into a Module of the MyMacros - Standard directory, then the macros will be available for all Calc documents.
Thank you very much! I’ll check out the MRI and XrayTool if I can find them.