[Solved] Updating Linked Cell of a Combo Box

[Sulochana]

Hi there,

I am new to writing Calc Macros.

I have a workbook with about 200 combo boxes (one below the other included in cells). When users are using this, they will cut, delete and insert lines here and there and the linked cells order will be disturbed.

I want to write a macro which can update the linked cell of the combo box to the existing cell where the combo box is residing for all combo boxes available.

It is better if I can store it in the sheet where it will run automatically when the sheet is selected.

Really appreciate your support.

Thanks a lot.

[mauriciobaeza]

If you attach a sample file we can help you better ...
I do not think it's a good idea to use many ComboBox

Best regards

[pitonyak]

Played with this for a bit (since I have no idea how to do it). My first try was as follows:

Code: Select all

  Dim sName$
  Dim oSheet
  Dim oForm
  Dim oControlDataModel
  Dim oControl
  Dim BoundCell
  Dim i As Long
  
  oSheet = ThisComponent.getSheets().getByname("Sheet1")
  ' Assume there is only one form.
  oForm = oSheet.getDrawPage().getForms().getByIndex(0)
  'Inspect oForm
  For i = LBound(oForm.ElementNames) To UBound(oForm.ElementNames)
    oControlDataModel = oForm.getByName(oForm.ElementNames(i))
    If oControlDataModel.SupportsService("com.sun.star.awt.UnoControlComboBoxModel") Then
      BoundCell = oControlDataModel.ValueBinding.BoundCell
      BoundCell.Column = 6
      BoundCell.Row=13
      BoundCell.Sheet=0
      oControlDataModel.ValueBinding.BoundCell = BoundCell
      'oControl = ThisComponent.CurrentController.getControl(oControlDataModel)
      'Inspect oControlDataModel
      'Inspect oControl
    End If
  Next

Sadly, this fails because the BoundCell property is read-only..... Then I decided to stop because I need to feed the kids.

I did not see how to find the anchor. But, if you look at the draw page, you can enumerate the objects and find controls based on service "com.sun.star.drawing.ControlShape" and this contains an anchor which contains the Anchor. The anchor contains the object CellAddress, which you can use to find the column, row, and sheet.

From the ControlShape you can access the Control property to get the control model, so that is probably a better starting place than enumerating the forms. So, I know how to tell where the control is located, but I do not know how to change the cell to which it refers. Interested if you make any progress.

[Sulochana]

Hi,

Tried to attach the file but it says the file is too big.

Thanks Andrew for trying to help.

I am very new to writing macro's and you are a Guru. I referred your book for many things and thank you for that. I will try to work this out the way you suggested.

I have to say, If you are not sure how to do it (you are very humble), I don't have a chance.

Thanks again,

Sulochana

[pitonyak]

I used my TypesDocCanCreate macro (it is in OOME) to figure out what it could create.... It was still an annoying and painful to figure out how to do this. You must create your own instance and initialize the "read-only" attribute at creation time. I think that you can also use the initialize method after creation but before using it, but I did not try that.

Is this a sufficient hint in the right direction (since this example is still using the control from the form rather than enumerating the shapes on the draw-page and checking the referenced control type).

Code: Select all

Sub FindControl
  Dim sName$
  Dim oSheet
  Dim oForm
  Dim oControlDataModel
  Dim oControl
  Dim oService
  Dim i As Long
  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
  
  oSheet = ThisComponent.getSheets().getByname("Sheet1")
  'TypesDocCanCreate(ThisComponent)
  
  'Inspect oSheet.getDrawPage().getByIndex(0)
  
  ' Assume there is only one form.
  oForm = oSheet.getDrawPage().getForms().getByIndex(0)
  For i = LBound(oForm.ElementNames) To UBound(oForm.ElementNames)
    oControlDataModel = oForm.getByName(oForm.ElementNames(i))
    If oControlDataModel.SupportsService("com.sun.star.awt.UnoControlComboBoxModel") Then
      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)
      oControlDataModel.ValueBinding = oService
      'oControl = ThisComponent.CurrentController.getControl(oControlDataModel)
    End If
  Next
End Sub

Many times I have pondered how to solve this type of problem..... Like when I want to use a check box in each cell to mean yes/no but the controls do not sort with the data and what happens when I insert a row. I never pursued it to see if it just works or if it needs this type of macro for cleanup.... Either way, interested in what you end up with.

[Sulochana]

Dear Andrew,

Tested the coding and we are getting close. Thanks a lot for the support.

I changed the Row (with a variable "Row") and column to suit the requirement.

When the loop is running, Variable "Row" will change from 1 to i and that will be the row number. Combo box linked cell also changes but it does not go in the order of the combo boxes. E.G. Combobox1 will be linked to row number 96 and not 1.

This is the only bit that needs to be sorted. The combo box is Anchored to Cell. If we can get the cell reference of the cell the combo box is anchored to, we can use that as the row number in the macro.

Please find the files (made small) for your understanding with the macro.

Thanks again

[pitonyak]

Sounds like you are getting the hang of it....

[Sulochana]

I am stuck Andrew.

Some help in getting the row number to which the Combo box is anchored is needed.

Can you help? please...?

Really appreciate your support.

Sulochana

[pitonyak]

How are the controls anchored? I assumed that they were anchored to a cell. If they are anchored to the sheet, then this becomes very difficult. I will start by providing a few functions that I have just sitting around, not that I know if they will be of use to you or not...

Code: Select all

' Gets the Shape of a Control( e. g. to reset the size or Position of the control
' Parameters:
' The 'oContainer' is the Document or a specific sheet of a Calc - Document
' 'CName' is the Name of the Control
Function GetControlShape(oContainer as Object,CName as String)
Dim i as integer
Dim aShape as Object
   For i = 0 to oContainer.DrawPage.Count-1
      aShape = oContainer.DrawPage(i)
      If HasUnoInterfaces(aShape, "com.sun.star.drawing.XControlShape") then
         If ashape.Control.Name = CName then
            GetControlShape = aShape
            exit Function
         End If
      End If
   Next
End Function 

Function FindCellWithControl(oDrawPage, oControl)
  Dim oShape
  Dim oAnchor
  oShape = FindShapeForControl(oDrawPage, oControl)
  If Not IsEmpty(oShape) Then
    If oShape.getAnchor().supportsService("com.sun.star.sheet.SheetCell") Then
      FindCellWithControl = oShape.getAnchor()
    End If
  End If
End Function

Function FindShapeForControl(oDrawPage, oControl)
  Dim i
  Dim oShape
  For i = 0 To oDrawPage.getCount()
    oShape = oDrawPage.getByIndex(i)
    If oShape.supportsService("com.sun.star.drawing.ControlShape") Then
      If EqualUNOObjects(oControl, oShape.Control) Then
        FindShapeForControl = oShape
        Exit Function
      End If
    End If
  Next
End Function

Sub ButtonHandler(oEvent)
  Dim oControlModel
  Dim oParent
  Dim oCell
  
  'Print oEvent.Source.Model.getName()
  oControlModel = oEvent.Source.Model
  
  oParent = oControlModel.getParent()
  Do While NOT oParent.supportsService("com.sun.star.sheet.SpreadsheetDocument") 
    oParent = oParent.getParent()
  Loop 
  
  oCell = FindCellWithControl(oParent.getCurrentController().getActiveSheet().getDrawPage(), oControlModel)
  If NOT IsEmpty(oCell) Then
    Print "Control is in cell " & oCell.AbsoluteName
  Else
    Pritn "Unable to find cell with control"
  ENd If
End Sub

And now for enumerating the combo box controls

Code: Select all

  Dim oSheet
  Dim oDraw
  Dim oShape
  Dim i As Long
  
  oSheet = ThisComponent.Sheets.getByIndex(0)
  oDraw = oSheet.getDrawPage()
  For i = 0 To oDraw.getCount() - 1
    oShape = oDraw.getByIndex(0)
    If HasUnoInterfaces(oShape, "com.sun.star.drawing.XControlShape") Then
      If oShape.getAnchor().supportsService("com.sun.star.sheet.SheetCell") Then
        ' The anchor IS the cell, so we can grab the cell address here
        ' Things are tricky if it is not anchored to the cell
        ' oShape.getAnchor().CellAddress

        ' Get the control model
        If oShape.Control.supportsServcie("com.sun.star.awt.UnoControlComboBoxModel") Then
          ' The control model IS a combo box!
          'Inspect oShape.Control
        End If
      End If
    End If
  Next

Hope This Helps!

[Sulochana]

Thanks Andrew, it certainly helped.

Yes all combo boxes are Anchored to Cell. I managed to get the cell reference and link it to the previous macro. Please see the code below.

We are almost there. However, there is one small issue. When I run the macro, it still does not link the combo boxes to the anchored cell. I tried to find where the problem is for about 3 hours with no luck.

Obviously I am missing something. Can you please just look at the coding and point me in the right direction? I have attached the latest file for your review.

Really appreciate your support.

Code: Select all

Sub FindControl2
 
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")


  Dim sName$
  Dim oSheet
  Dim oForm
  Dim oControlDataModel
  Dim oControl
  Dim oService
  Dim i As Long
  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
  Dim Row
  Dim oDraw
  Dim oShape
  Dim Column
 
  oSheet = ThisComponent.getSheets().getByname("WPC")
  oForm = oSheet.getDrawPage().getForms().getByIndex(0)
  
  For i = LBound(oForm.ElementNames) To UBound(oForm.ElementNames)
    oControlDataModel = oForm.getByName(oForm.ElementNames(i))
    If oControlDataModel.SupportsService("com.sun.star.awt.UnoControlComboBoxModel") Then
    oDraw = oSheet.getDrawPage()
    oShape = oDraw.getByIndex(i)
   		 If oShape.getAnchor().supportsService("com.sun.star.sheet.SheetCell") Then
   		 Row = oShape.getAnchor().CellAddress.row
   		 Column = oShape.getAnchor().CellAddress.column
   		 End if
      oCellAddress.Column = Column
      oCellAddress.Row= Row
      oCellAddress.Sheet= 0
      arg(0).Name = "BoundCell"
      arg(0).Value = oCellAddress
      oService = ThisComponent.createInstanceWithArguments("com.sun.star.table.CellValueBinding", arg)
      oControlDataModel.ValueBinding = oService
      End If
 
  Next


end sub

[pitonyak]

Your code looks to me like it should work.... I threw this together and it does work

Code: Select all

  Dim oSheet
  Dim oDraw
  Dim oShape
  Dim i As Long
  Dim iSheet As Long
  Dim oBoundCell
  Dim oAnchor
  Dim arg(0) as new com.sun.star.beans.NamedValue
  Dim oValueBinding
  
  iSheet = 0
  
  oSheet = ThisComponent.Sheets.getByIndex(iSheet)
  oDraw = oSheet.getDrawPage()
  For i = 0 To oDraw.getCount() - 1
    oShape = oDraw.getByIndex(0)
    If HasUnoInterfaces(oShape, "com.sun.star.drawing.XControlShape") Then
      oAnchor = oShape.getAnchor()
      If oAnchor.supportsService("com.sun.star.sheet.SheetCell") Then
        ' The anchor IS the cell, so we can grab the cell address here
        ' Things are tricky if it is not anchored to the cell
        ' oShape.getAnchor().CellAddress

        ' Get the control model
        'Inspect oShape
        If oShape.Control.supportsService("com.sun.star.awt.UnoControlComboBoxModel") Then
          ' The control model IS a combo box!
          oBoundCell = oShape.Control.ValueBinding.BoundCell
          
          If oBoundCell.Row <> oAnchor.CellAddress.Row OR oBoundCell.Column <> oAnchor.CellAddress.Column OR oBoundCell.Sheet <> oAnchor.CellAddress.Sheet Then
            arg(0).Name = "BoundCell"
            arg(0).Value = oAnchor.CellAddress
            oValueBinding = ThisComponent.createInstanceWithArguments("com.sun.star.table.CellValueBinding", arg)
            oShape.Control.ValueBinding = oValueBinding
          End If
        End If
      End If
    End If
  Next

If I have a control anchored to cell B13 and I insert a new row on or before cell B13, then cell B13 becomes cell B14. The bound cell for the control is still cell B14 (at least when I do it).
If my control anchored to cell B13 references cell A13, then after the macro it references cell B13 and the control will then have the value that cell B13 had rather than what A13 had.

[Sulochana]

I don't know how to thank you Andrew. You are a legend and I owe you big time. You make the world a better place.

The coding works perfectly. I made 2 minor changes so it will run on any sheet and will work with any number of combo boxes (replaced 0 with i)

Thanks again and wish you success in everything you do.

Code: Select all

Sub UpdateComboBoxLinkedCell()
  Dim oSheet
  Dim oDraw
  Dim oShape
  Dim i As Long
  Dim iSheet As Long
  Dim oBoundCell
  Dim oAnchor
  Dim arg(0) as new com.sun.star.beans.NamedValue
  Dim oValueBinding
  
  iSheet = ThisComponent.getCurrentController().getActiveSheet().getRangeAddress().sheet
  oSheet = ThisComponent.Sheets.getByIndex(iSheet)
  oDraw = oSheet.getDrawPage()
  For i = 0 To oDraw.getCount() - 1
    oShape = oDraw.getByIndex(i)
    If HasUnoInterfaces(oShape, "com.sun.star.drawing.XControlShape") Then
      oAnchor = oShape.getAnchor()
      If oAnchor.supportsService("com.sun.star.sheet.SheetCell") Then
        ' The anchor IS the cell, so we can grab the cell address here
        ' Things are tricky if it is not anchored to the cell
        ' oShape.getAnchor().CellAddress

        ' Get the control model
        'Inspect oShape
        If oShape.Control.supportsService("com.sun.star.awt.UnoControlComboBoxModel") Then
          ' The control model IS a combo box!
          oBoundCell = oShape.Control.ValueBinding.BoundCell
          
          If oBoundCell.Row <> oAnchor.CellAddress.Row OR oBoundCell.Column <> oAnchor.CellAddress.Column OR oBoundCell.Sheet <> oAnchor.CellAddress.Sheet Then
            arg(0).Name = "BoundCell"
            arg(0).Value = oAnchor.CellAddress
            oValueBinding = ThisComponent.createInstanceWithArguments("com.sun.star.table.CellValueBinding", arg)
            oShape.Control.ValueBinding = oValueBinding
          End If
        End If
      End If
    End If
  Next
End sub

[pitonyak]

Your welcome, glad it helped.