[SOLVED] Macro to link checkboxes cell at Lib Calc

Macro to link checkboxes cell at Lib Calc

Context: I'm trying to learn excel, for personal and profissional purposes. As I can't afford excel at the moment I'm using Lib Calc which I found very similar. I learnt many things that can be easily used in both programs, but with macros it didn't work so well.

Problem: When I was trying to make a macro that links the checkboxes to a cell of my spreadsheet automatically I couldn't find many useful sites that taught how to make it on Lib Calc, just in excel, so I took a ready-made code to excel and tried to make run on Lib Calc as I read the documentation, but I couldn't make it run. The code that i took was this:

Sub LinkCheckBoxes()
Dim chk As CheckBox
Dim lCol As Long
lCol = 2 'number of columns to the right for link
For Each chk In ActiveSheet.CheckBoxes
   With chk
      .LinkedCell = _
         .TopLeftCell.Offset(0, lCol).Address
   End With
Next chk
End Sub

Source: https://contexturesblog.com/archives/2014/01/14/click-a-cell-to-check-yes-or-no/

I've tried many thing as I read the documentation, but nothing worked and I didn't even know where is the problem, so anyone knows how can I proceed?

Solution

To do this dynamically in a macro try:


Sub LinkCheckBoxes

    Dim oLinkedCell as new com.sun.star.table.CellAddress
    Dim nv as new com.sun.star.beans.NamedValue

    sheet = ThisComponent.CurrentController.ActiveSheet
    form = sheet.DrawPage.Forms.getByIndex(0)
    
    for i = 0 to form.count - 1
        chk = form.getByIndex(i)
        oLinkedCell.Sheet = 0
        oLinkedCell.Column = 1
        oLinkedCell.Row = i
        nv.Name = "BoundCell"
        nv.value = oLinkedCell
        link = ThisComponent.createInstance("com.sun.star.table.CellValueBinding")
        link.Initialize(Array(nv))
        chk.setValueBinding(link)
    next
End Sub