CODEWITHSUNDEEP
macroslibreoffice-calcunolibreoffice-basic
ZioByte
ZioByte

Reputation: 3004

link Open/Libre Office button to cell and reference cell in macro

I would like to add a [set of] standardized macro[s] to some of the cells of a custom spredsheet (Open/Libre/Star Office).

Said macro should be activated using a Form PushButton dropped into the relevant cell[s].

I experience several problems all relative to the access of the "relevant cell":

  1. If I try to Anchor to Cell a PushButton it goes to A1 and not to currently selected cell.
  2. I can connect a Basic fragment to the button, but I found no way to retrieve the "relevent cell" (i.e.: the cell containing the button).

What I am trying to do (as a first working example) is to add a button to increment the numeric value of the cell (possibly disabling direct editing; I want that value to go up by one at each button press and no way to otherwise change cell).

Is such a thing possible at all?

Any example (or pointer to docs) very welcome.

NOTE: This question gives some hints on how to solve problem in VBA (Excel), but I found nothing for [L|O|S]Office

Upvotes: 1

Views: 2400

Answers (1)

Andrew
Andrew

Reputation: 836

You can find the cell containing the button from a handler as follows:

Sub ButtonHandler(oEvent)

  Dim sControlName$
  Dim oSheet
  Dim nCount As Long
  Dim i As Long
  Dim oPage
  Dim oShape
  Dim oAnchor

  sControlName = oEvent.source.model.Name
  oSheet = thiscomponent.currentcontroller.activesheet
  nCount = oSheet.drawpage.count
  oPage = oSheet.drawpage

  For i = 0 To nCount - 1
    oShape = oPage.getbyindex(i)
    'oControlShape = oPage.getbyindex(i).control
    If (oShape.supportsService("com.sun.star.drawing.ControlShape")) Then
      If oShape.control.Name = sControlName Then
        oAnchor = oShape.anchor
        If (oAnchor.supportsService("com.sun.star.sheet.SheetCell")) Then
          Print "Button is anchored in cell: " + oAnchor.AbsoluteName
          Exit For
        End If
      End If
    End If
  Next i
End Sub

I know, it is not pretty is it? I added significant error checking.If you then want to know what cell was active when you clicked the button, you can call this routine

Sub RetrieveTheActiveCell()
  Dim oOldSelection 'The original selection of cell ranges
  Dim oRanges       'A blank range created by the document
  Dim oActiveCell   'The current active cell
  Dim oConv         'The cell address conversion service
  Dim oDoc
  oDoc = ThisComponent

  REM store the current selection
  oOldSelection = oDoc.CurrentSelection

  REM Create an empty SheetCellRanges service and then select it.
  REM This leaves ONLY the active cell selected.
  oRanges = oDoc.createInstance("com.sun.star.sheet.SheetCellRanges")
  oDoc.CurrentController.Select(oRanges)

  REM Get the active cell!
  oActiveCell = oDoc.CurrentSelection

  oConv = oDoc.createInstance("com.sun.star.table.CellAddressConversion")
  oConv.Address = oActiveCell.getCellAddress
  Print oConv.UserInterfaceRepresentation
  print oConv.PersistentRepresentation

  REM Restore the old selection, but lose the previously active cell
  oDoc.CurrentController.Select(oOldSelection)
End Sub

Upvotes: 1

Related Questions