Site Tools


software:libreoffice:calc:macros:basic:button

Using buttons within calc to perform basic function

This describes how to write some code and setup your worksheet to perform a certain action when a button is pressed.

Create a button in cell

View→Toolbars→Form controls
Go into design mode (This disables any button functions) Select from the form controls Push Button
Draw this button in a cell.

Configure button

Change the label to something meaningful to the user:

  • Right click button→ click on “Control” - this will open the “Properties” dialog → select the “Label” box on the “General” tab - this is used to set the text to be displayed on the button
  • Assigning a macro to the button→click on the “Events” tab in the “Properties” dialog→click on the button on the same row as “Mouse button pressed” - this opens the “Assign action” dialog→click on “Macro” - this opens the “Macro Selector” dialog, and this should be used to select the correct macro for the button

Exit design mode

All buttons will be useless to a user of the spreadsheet if the design mode is active. To change from design mode to active mode two more steps are required:

  • the design mode is turned off by clicking the “Design Mode On/Off” icon (showing a pencil, ruler and set square) in the “Form Controls” toolbar

Basic code examples

elementary cell handling

Paste code below into: Tools→Macros→Organize Macros→LibreOffice.org Basic…

REM Sheet containing the parts count report view
Public Const SHEET_NAME As String = "NAME_OF_THIS_SHEET"

Sub telop
    Dim oDoc As Object
    Dim oSheet As Object
    Dim oCell_1 As Object
    Dim oCell_2 As Object
    Dim cell As Object
    oDoc = ThisComponent
    oSheet = oDoc.getSheets().getByName(SHEET_NAME)
    oCell_1 = ThisComponent.Sheets(0).getCellRangeByName("A5")
    oCell_1.Value = 100
    oCell_2 = ThisComponent.Sheets(0).getCellRangeByName("A6")
    oCell_2.Value = 200
    Cell = thisComponent.Sheets(0).getCellByPosition(0, 7) 
    Cell.Formula = "=SUM(A5:A6)"
End Sub

Create button to quickly navigate to certain sheet

Or an example to create a button which, when clicked, jumps to a designated sheet:

  1. Copy-paste the script gotoSheet from below in the basic editor, which is reachable via the menu: Tools → Macros → Organize Macros → Basic
  2. In a worksheet, create a button via the menu: View → Toolbars → Form Controls
  3. Click on the Design mode button
  4. Add a push button
  5. Right click on the button placed and select the Control Properties…
  6. In the General tab, Name field, add the name of the target sheet (As appears at the bottom tab of each sheet.)
  7. In the General tab, Label field, add a descriptive name which should appear on the button
  8. In the Events tab, Mouse button pressed, click on the … and navigate to the script which you copy-pasted earlier.
  9. Leave the design mode.
Sub gotoSheet(oEvent)
    Dim oCaller As Object
    Dim sName As String
    Dim oSheet As Object
    oCaller=oEvent.Source.Model
    sName = oCaller.Name
    oSheet = ThisComponent.getSheets().getByName(sName)
    ThisComponent.CurrentController.setActiveSheet(oSheet)
End Sub

Further reading

software/libreoffice/calc/macros/basic/button.txt · Last modified: 2022/01/24 00:53 by admin