[Solved] Calc basic macro to add a button to a cell

[Amazed]

How can I add a button to a cell with an OOo Basic macro?

in sheet A, I have data for objecttype A, amongst it, a row# referring to another sheet with other objecttypes
With the macro I want to put buttons around the rownumbers, to navigate to the row# in the other sheet

I know how to make a button in a cell manually.
In http://api.openoffice.org/docs/common/r ... utton.html
I read, that a button is a component in a form.
I guess, that a form with the button needs to be made and somehow be connected to the cell.

[FJCC]

Rather than use buttons, it would be easier to use a hyperlink function, if there is a cell available in which to put it. For example, this function displays the text "Jump" and takes you to cell E7 of Sheet2

Code: Select all

=HYPERLINK("#Sheet2.E7";"Jump")

If you have to use buttons, here is some code I have stored, but I'm sure I didn't write it. In its present form it adds a button to the first sheet and all the button does is call a macro that prints the message "HERE". It is not clear to me how to position the button over a given cell. Adjusting the aPoint.X and aPoint.Y values will get the job done, but I don't know how to calculate the correct values for an arbitrary cell.

Code: Select all

Sub CreateButton 
  oDoc = ThisComponent
  oSheet = oDoc.Sheets.getByIndex(0)
  oDrawPage = oSheet.DrawPage  'Was oDrawPage = oDoc.getDrawPage()
 
  sScriptURL = "vnd.sun.star.script:Standard.Module1.ButtonPushEvent?language=Basic&location=document"
  oButtonModel = AddNewButton("Button_1", "Button_1", oDoc, oDrawPage)
  oForm = oDrawPage.getForms().getByIndex(0)
  ' find index inside the form container
  nIndex = GetIndex(oButtonModel, oForm)
  AssignAction(nIndex, sScriptURL, oForm)
End Sub

' assign sScriptURL event as css.awt.XActionListener::actionPerformed.
' event is assigned to the control described by the nIndex in the oForm container
Sub AssignAction(nIndex As Integer, sScriptURL As String, oForm As Object)
  aEvent = CreateUnoStruct("com.sun.star.script.ScriptEventDescriptor")
  With aEvent
    .AddListenerParam = ""
    .EventMethod = "actionPerformed"
    .ListenerType = "XActionListener"
    .ScriptCode = sScriptURL
    .ScriptType = "Script"
  End With
 
  oForm.registerScriptEvent(nIndex, aEvent)
End Sub


Function AddNewButton(sName As String, sLabel As String, oDoc As Object, oDrawPage As Object) As Object
  oControlShape = oDoc.createInstance("com.sun.star.drawing.ControlShape")

  aPoint = CreateUnoStruct("com.sun.star.awt.Point")
  aSize = CreateUnoStruct("com.sun.star.awt.Size")
  aPoint.X = 1000
  aPoint.Y = 1000
  aSize.Width = 3000
  aSize.Height = 1000
  oControlShape.setPosition(aPoint)
  oControlShape.setSize(aSize)

  oButtonModel = CreateUnoService("com.sun.star.form.component.CommandButton")
  oButtonModel.Name = sName
  oButtonModel.Label = sLabel
 
  oControlShape.setControl(oButtonModel)
  oDrawPage.add(oControlShape)
 
  AddNewButton = oButtonModel
End Function


Function GetIndex(oControl As Object, oForm As Object) As Integer
  Dim nIndex As Integer
  nIndex = -1
  For i = 0 To oForm.getCount() - 1 step 1
    If EqualUnoObjects(oControl, oForm.getByIndex(i)) Then
      nIndex = i
      Exit For
    End If
  Next
  GetIndex = nIndex
End Function


Sub ButtonPushEvent(ev as com.sun.star.awt.ActionEvent)
  Print "HERE"
End Sub

[Villeroy]

Form controls are static controls for dynamic row sets coming from a database. What spreadsheet addicts want are dynamic controls for static cells.
A cell having the following formula ...

Code: Select all

=HYPERLINK("vnd.sun.star.script:Standard.Module1.Main?language=Basic&location=document")

can be formatted to look like a button and you can copy/drag it across the sheet.
With some additional arguments you can pass arbitrary arguments to the called macro, such as:

Code: Select all

=HYPERLINK(
"vnd.sun.star.script:Standard.Module1.Main
?language=Basic&location=document
&S="&SHEET()&"&R="&ROW()&"&C="&COLUMN()
)

Will pass the following argument sURL to your Main routine:

Code: Select all

Sub Main(sURL)
print sURL
End Sub

prints

Code: Select all

vnd.sun.star.script:Standard.Module1.Main?language=Basic&location=document&S=2&R=3&C=4

when called from sheet #2, row #4, column #4

The following helper extracts the argument values:

Code: Select all

REM returns "" if no value was found for name.
Function getArgumentFromURL(sURL$,sName$) as String
on error goto exitErr:
Dim iStart%, i%, l%, sArgs$, a()
   iStart = instr(sURL, "?")
   l = len(sName)
   if (iStart = 0) or (l = 0) then exit function
   ' sArgs behind "?":
   sArgs = mid(sURL, iStart +1)
   a() = split(sArgs, "&")
   for i = 0 to uBound(a())
      ' not case sensitive:
      if instr(1, a(i), sName &"=", 1) = 1 then
         getArgumentFromURL = mid(a(i), l +2)
         exit for
      endif
   next
exitErr:
' return ""
End Function

[Amazed]

Thanks, guys. Overwhelming. I think I'll use the hyperlink. And nice to know where to find a way to automated addition of a button.

[oonk]

Rather than use buttons, it would be easier to use a hyperlink function, if there is a cell available in which to put it. For example, this function displays the text "Jump" and takes you to cell E7 of Sheet2

Code: Select all

=HYPERLINK("#Sheet2.E7";"Jump")

If you have to use buttons, here is some code I have stored, but I'm sure I didn't write it. In its present form it adds a button to the first sheet and all the button does is call a macro that prints the message "HERE". It is not clear to me how to position the button over a given cell. Adjusting the aPoint.X and aPoint.Y values will get the job done, but I don't know how to calculate the correct values for an arbitrary cell.

Code: Select all

Sub CreateButton 
  oDoc = ThisComponent
  oSheet = oDoc.Sheets.getByIndex(0)
  oDrawPage = oSheet.DrawPage  'Was oDrawPage = oDoc.getDrawPage()
 
  sScriptURL = "vnd.sun.star.script:Standard.Module1.ButtonPushEvent?language=Basic&location=document"
  oButtonModel = AddNewButton("Button_1", "Button_1", oDoc, oDrawPage)
  oForm = oDrawPage.getForms().getByIndex(0)
  ' find index inside the form container
  nIndex = GetIndex(oButtonModel, oForm)
  AssignAction(nIndex, sScriptURL, oForm)
End Sub

' assign sScriptURL event as css.awt.XActionListener::actionPerformed.
' event is assigned to the control described by the nIndex in the oForm container
Sub AssignAction(nIndex As Integer, sScriptURL As String, oForm As Object)
  aEvent = CreateUnoStruct("com.sun.star.script.ScriptEventDescriptor")
  With aEvent
    .AddListenerParam = ""
    .EventMethod = "actionPerformed"
    .ListenerType = "XActionListener"
    .ScriptCode = sScriptURL
    .ScriptType = "Script"
  End With
 
  oForm.registerScriptEvent(nIndex, aEvent)
End Sub


Function AddNewButton(sName As String, sLabel As String, oDoc As Object, oDrawPage As Object) As Object
  oControlShape = oDoc.createInstance("com.sun.star.drawing.ControlShape")

  aPoint = CreateUnoStruct("com.sun.star.awt.Point")
  aSize = CreateUnoStruct("com.sun.star.awt.Size")
  aPoint.X = 1000
  aPoint.Y = 1000
  aSize.Width = 3000
  aSize.Height = 1000
  oControlShape.setPosition(aPoint)
  oControlShape.setSize(aSize)

  oButtonModel = CreateUnoService("com.sun.star.form.component.CommandButton")
  oButtonModel.Name = sName
  oButtonModel.Label = sLabel
 
  oControlShape.setControl(oButtonModel)
  oDrawPage.add(oControlShape)
 
  AddNewButton = oButtonModel
End Function


Function GetIndex(oControl As Object, oForm As Object) As Integer
  Dim nIndex As Integer
  nIndex = -1
  For i = 0 To oForm.getCount() - 1 step 1
    If EqualUnoObjects(oControl, oForm.getByIndex(i)) Then
      nIndex = i
      Exit For
    End If
  Next
  GetIndex = nIndex
End Function


Sub ButtonPushEvent(ev as com.sun.star.awt.ActionEvent)
  Print "HERE"
End Sub

I got an error message.
What should I do next ?

[FJCC]

The macro works for me in both OpenOffice and LibreOffice. If you upload a document where it does not work, someone could look for the problem. To upload a file, click Post Reply and look for the Upload Attachment tab just below the box where you type a response.

[Zizi64]

From the attached picture:

Library1.Module1.AddnewButton

Try to place the macro into the Standard library (of the document, or of the software.)
How you tried to launch the macro?

Please upload your sample file here.

[oonk]

From the attached picture:

Library1.Module1.AddnewButton

Try to place the macro into the Standard library (of the document, or of the software.)
How you tried to launch the macro?

Please upload your sample file here.

Thank you so much, the result is a button in a sheet, not in a cell.
Can you please help me to create a button in each cell ?

[Zizi64]

the result is a button in a sheet, not in a cell.
Can you please help me to create a button in each cell ?

It is not possible to place an object INSIDE a cell. The objects are located on a graphic layer. But you can anchor and align an object to a cell (with same size an position).

Please upload your ODF type sample file here together with the embedded macro code.


sample code:
viewtopic.php?f=9&t=34544

[oonk]

Please upload your ODF type sample file here together with the embedded macro code.

In Sheet 1 of attached file, I'd like to add a button in cells on column A.
When user clicks, the additional details will pop up.

Thank you so much in advance.

[Villeroy]

yet another invoicing spreadsheet that will never fly.

[Zizi64]

In Sheet 1 of attached file, I'd like to add a button in cells on column A.
When user clicks, the additional details will pop up.

You can achieve this with a Hyperlink launched macro - instead of the buttons.

Here is the modified sample file:

LibreOfficeCalcSampleCodes_ButtonInSheet_Zizi64.ods

(24.03 KiB) Downloaded 506 times