I want to programmatically change the linked cell in a formatted text control on a spreadsheet form using librebasic
I can get the control object, but can’t find how to address and thus change the “linked cell” property/method.
eg. something like… if the formatted text control object is mydate
mydate.linkedcell=“sheetname.D4”
Try with:
Sub CuadroTexto5()
Dim oDoc As Object
Dim oHojaActiva As Object
Dim oPaginaDibujo As Object
Dim oFormularios As Object
Dim oFormulario As Object
Dim otxtNombre As Object
Dim oDirCeldaVinculada As Object
Dim mOpc(0) As New "com.sun.star.beans.NamedValue"
Dim oVinculo As Object
oDoc = ThisComponent
oHojaActiva = oDoc.getCurrentController.getActiveSheet()
oPaginaDibujo = oHojaActiva.getDrawPage()
oFormularios = oPaginaDibujo.getForms()
oFormulario = oFormularios.getByName( "FORM_NAME" )
otxtNombre = oFormulario.getByName("CONTROL_NAME")
REM Link cell
oDirCeldaVinculada = oHojaActiva.getCellByPosition(1,4).getCellAddress
REM Propery linked
mOpc(0).Name = "BoundCell"
mOpc(0).Value = oDirCeldaVinculada
REM Create instance
oVinculo= oDoc.createInstanceWithArguments("com.sun.star.table.CellValueBinding", mOpc())
REM And set linked
otxtNombre.setValueBinding( oVinculo )
End Sub
Best regards
thanks very much. I’ll try that out tomorrow. and post back.
I tried this but got an exception at the createInstanceWithArguments line (com.sun.star.uno.Exception with no Message)
changing the line to
oVinculo= oDoc.createInstanceWithArguments("com.sun.star.table.CellValueBinding", mOpc(0))
I get an exception (com.sun.star.lang.IllegalArgumentException with Message cannot coerce argument type during corereflection call! )
I did try msgbox "mOpc values are "str(mOpc(0).Value.Sheet)+" "+str(mOpc(0).Value.Column)+" "+str(mOpc(0).Value.Row)
which shows the correct cell address numbers, so everything looks ok just before that line.
I spotted an error in my code (I’ll blame autocorrect as it kept changing variable (object) declarations when I typed them), and I removed the double quotes from the namedvalue declaration and it worked.! I posted my solution for reference for others. thanks again, mauricio
the following two functions provide a solution.
Function getcell(sheetname as string,x as integer,y as integer) as object 'returns a cell object. no error checking is done 'x and y are column and row number starting at 0. Dim hmy_cell as object hmy_cell=ThisComponent.getSheets().getByName(sheetname).getCellByPosition(x,y) getcell = hmy_cell End Function sub setcontrollinkedcell(sheetname As string,cellx As long, celly As long,mycontrol As object) 'this takes an object (text control or formatted control etc.) and links it to the cellx,celly on sheet sheetname Dim mylink As Object,mycell As object Dim mylocation(0) As New com.sun.star.beans.NamedValue mylocation(0).Name="BoundCell" mycell=getcell(sheetname,cellx,celly) mylocation(0).Value=mycell.getCellAddress() mylink=ThisComponent.createInstanceWithArguments("com.sun.star.table.CellValueBinding", mylocation()) mycontrol.setValueBinding(mylink) End Sub
removing the double quotes around the namedvalue declaration seemed to do the trick (that and spotting where libreoffice had auto-corrected what I typed into something entirely different!).
many thanks to mauricio for pointing me in the right direction.