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