[Solved] Link checkbox to a spreadsheet cell using macros

[PuzzleJM]

Hello, I'm on Windows 10 Pro and on OpenOffice 4.1.11.
I wrote a macro to create some checkboxes inside a sheet but I cannot find how to link each of them to a cell; I did read that maybe I can't and the attribute is only read-only but since I'm not sure I'm asking here.
Here's my code right now but I'm struggling to find the way to link a checkbox to a specific cell.

Code: Select all

Sub CreateCheckboxesInCells()
    Dim oDoc As Object
    Dim oSheet As Object
    Dim oDrawPage As Object
    Dim oCheckboxModel As Object
    Dim cellAddress As String
    Dim cellPosition As Object

    oDoc = ThisComponent
    oSheet = oDoc.Sheets.getByName("Individus")
    oDrawPage = oSheet.DrawPage

    ' Loop to create checkboxes from "Checkbox_1" to "Checkbox_5" in cells B2 to B6
    For i = 1 To 5
        ' Specify the cell address (e.g., B2, B3, etc.)
        cellAddress = "B" & (i + 1) ' Adding 1 to start from B2

        ' Get the position of the cell
        cellPosition = oSheet.getCellRangeByName(cellAddress).Position

        ' Create a new checkbox model with a unique name
        oCheckboxModel = AddNewCheckbox("Checkbox_" & i, "Homme", oDoc, oDrawPage, cellPosition)
    Next i
End Sub

Function AddNewCheckbox(sName As String, sLabel As String, _
    oDoc As Object, oDrawPage As Object, cellPosition As Object) As Object

    Dim oControlShape As Object
    Dim oButtonModel As Object

    ' Create ControlShape
    oControlShape = oDoc.createInstance("com.sun.star.drawing.ControlShape")

    ' Set position based on cell position
    oControlShape.setPosition(cellPosition)

    ' Set size (adjust as needed)
    Dim aSize As New com.sun.star.awt.Size
    aSize.Width = 3000
    aSize.Height = 1000
    oControlShape.setSize(aSize)

    ' Create CheckBox model
    oButtonModel = CreateUnoService("com.sun.star.form.component.CheckBox")
    oButtonModel.Name = sName
    oButtonModel.Label = sLabel

    ' Set control for ControlShape
    oControlShape.setControl(oButtonModel)

    ' Add ControlShape to DrawPage
    oDrawPage.add(oControlShape)

    ' Return the CheckBox model
    AddNewCheckbox = oButtonModel
End Function

I tried some things but to no avail.

Code: Select all

Dim oLinkedCell as new com.sun.star.table.CellAddress
	    oLinkedCell.Sheet = 1
	    oLinkedCell.Column = 0
	    oLinkedCell.Row = oRow
	    
	    Dim oNamedValue as new com.sun.star.beans.NamedValue
	    oNamedValue.Name  = "BoundCell"
	    oNamedValue.Value = oLinkedCell
	    oCVB = oDoc.createInstance("com.sun.star.table.CellValueBinding")
	    oCVB.Initialize(Array(oNamedValue))
	    
	    oForm = ThisComponent.Sheets(0).DrawPage.Forms.getByIndex(0)
		vField = oForm.getByName("Checkbox_" & i)'Name anpassen
	
	    vField.setValueBinding(oCVB)

I did find some sources like this one link : https://de.openoffice.info/viewtopic.php?t=35008
But yeah, if someone can explain to me how attributes/properties for checkboxes are created with macros I would be really grateful.

[FJCC]

Here is some code I cribbed from a post by Andrew Pitonyak.

Code: Select all

Sub CheckboxCell
Dim arg(0) as new com.sun.star.beans.NamedValue 'not a PropertyValue, a Named Value.
Dim oCellAddress As new com.sun.star.table.CellAddress
'Get the check box
oSheet = ThisComponent.Sheets.getByName("Sheet1")
oDP = oSheet.DrawPage
oForm = oDP.Forms.getByIndex(0)
oChkBox = oForm.getByName("Check Box 1")
'set the bound cell
oCellAddress.Column = 6
oCellAddress.Row=13
oCellAddress.Sheet=0
arg(0).Name = "BoundCell"
arg(0).Value = oCellAddress
oService = ThisComponent.createInstanceWithArguments("com.sun.star.table.CellValueBinding", arg)

oChkBox.ValueBinding = oService
End sub

[PuzzleJM]

Ok it works, thank you very much for showing me this example that's perfect.