Differences

This shows you the differences between two versions of the page.

--- software:libreoffice:calc:macros:basic:button [2016/05/17 11:43] – created admin
+++ software:libreoffice:calc:macros:basic:button [2022/01/24 00:53] (current) – admin
@@ Line 1: / Line 1: @@
 ====== 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 =====
+===== Basic code examples =====
+==== elementary cell handling ====
 Paste code below into:
 Tools->Macros->Organize Macros->LibreOffice.org Basic...
-<code>Sub telop
+<code>
+REM Sheet containing the parts count report view
+Public Const SHEET_NAME As String = "NAME_OF_THIS_SHEET"
-Dim oCell_1,oCell_2,Cell
+Sub telop
+    Dim oDoc As Object
-oCell_1 = thisComponent.Sheets(0).getCellRangeByName("A2")
+    Dim oSheet As Object
-oCell_1.Value = 100
+    Dim oCell_1 As Object
-oCell_2 = thisComponent.Sheets(0).getCellRangeByName("A3")
+    Dim oCell_2 As Object
-oCell_2.Value = 200
+    Dim cell As Object
-Cell = thisComponent.Sheets(0).getCellByPosition(0, 3)
+    oDoc = ThisComponent
-Cell.Formula = "=SUM(A2:A3)"
+    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
 </code>
+==== Create button to quickly navigate to certain sheet ====
+Or an example to create a button which, when clicked, jumps to a designated sheet:
+  - Copy-paste the script gotoSheet from below in the basic editor, which is reachable via the menu: Tools -> Macros -> Organize Macros -> Basic
+  - In a worksheet, create a button via the menu: View -> Toolbars -> Form Controls
+  - Click on the Design mode button
+  - Add a push button
+  - Right click on the button placed and select the Control Properties...
+  - In the General tab, Name field, add the name of the target sheet (As appears at the bottom tab of each sheet.)
+  - In the General tab, Label field, add a descriptive name which should appear on the button
+  - In the Events tab, Mouse button pressed, click on the ... and navigate to the script which you copy-pasted earlier.
+  - Leave the design mode.
+<code>
+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
+</code>
 ===== Further reading =====
   * [[http://dlc.sun.com/pdf/819-0439/819-0439.pdf|Programming Guide for BASIC (pdf)]] or [[http://wiki.services.openoffice.org/wiki/MediaWiki:Collections/BASIC_Guide]]
   * [[http://office-software.suite101.com/article.cfm/automating_openoffice_adding_buttons_to_calc]]
   * [[http://www.oooforum.org/forum/viewtopic.phtml?p=33522#33522|Cell style (color)]]

Auditeon

User Tools

Site Tools

Differences

Page Tools