How can I change formatted text control linked cell using basic

[timsoft]

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”

[mauricio]

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

[timsoft]

thanks very much. I’ll try that out tomorrow. and post back.

[timsoft]

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)

[timsoft]

which shows the correct cell address numbers, so everything looks ok just before that line.

[timsoft]

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

[timsoft]

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.