This is the second part of tutorial series of ‘Working with forms controls in LibreOffice/OpenOffice Calc’. First part can be found here. In the first part, a simple text box and a button is covered. This tutorial will cover all other basic controls – Label, Check Box, Option Box Combo Box, List Box – how to access values of those controls and how to recognize user selected values on those controls.
Open LibreOffice and create a dialog. Add a label, three check boxes, two option boxes, one list box, one combo box and a button. All these controls can be added via ToolBox (see below). You can access toolbox from menu: View --> Toolbars --> ToolBox.
Various controls in a toolbox:
Once you have added them, select and drag them as per your need inside the form. In this tutorial we will access the values/options selected in these controls and put them in calc cells. Reading the values is the most basic as these are needed for more complex tasks.
The form designed for this tutorial
Table of Contents
Checkbox
Checkboxes are a way to make user multiple choices. To get whether the check box is checked or not, one can use getState method. If it is checked it returns 1, otherwise 0. While designing, you can change the text displayed beside each check box using ‘Label’ field in general tab of the properties window.
chkBox1 = oDialog1.GetControl("CheckBox1")
chkBox2 = oDialog1.GetControl("CheckBox2")
chkBox3 = oDialog1.GetControl("CheckBox3")
if chkBox1.State = 1 then
oCell = ThisComponent.Sheets(0).getCellByPosition(1,1)
oCell.String = "Debian"
end if
if chkBox2.State = 1 then
oCell = ThisComponent.Sheets(0).getCellByPosition(1,2)
oCell.String = "Ubuntu"
end if
if chkBox3.State = 1 then
oCell = ThisComponent.Sheets(0).getCellByPosition(1,3)
oCell.String = "elementary"
end if
List Box and Combo box
List Box and Combo Box giving users an ability to select from a list of items. The only difference between these two are the way they display the items. Combo box have a drop down list and List Box have a list of items displayed.
To get the selection of a List Box, the function getSelectedItem() can be used.
To get the selected item from Combo box, the function SelectedText() can be used.
To pre-populate the list and combo, we will use addItem( function while loading the form for the first time.
lstBox1 = oDialog1.GetControl("ListBox1")
if lstBox1.getItemCount = 0 then
lstBox1.addItem("Mango",1)
lstBox1.addItem("Apple",2)
lstBox1.addItem("Orange",3)
end if
cmbBox1 = oDialog1.GetControl("ComboBox1")
if cmbBox1.getItemCount = 0 then
cmbBox1.addItem("500",1)
cmbBox1.addItem("1000",2)
cmbBox1.addItem("10000",3)
end if
oCell = ThisComponent.Sheets(0).getCellByPosition(1,5)
oCell.String = lstBox1.getSelectedItem()
oCell = ThisComponent.Sheets(0).getCellByPosition(1,6)
oCell.String = cmbBox1.SelectedText()
Option Box
Option box are options those presented to the user and only one of them can be selected. Unlike other applications, in LO, all the option boxes are grouped together by default that is added in the form.
The property State() of an option box returns True is the option is selected, otherwise, it returns False.
if optBtn1.State = True then
oCell = ThisComponent.Sheets(0).getCellByPosition(1,7)
oCell.String = "No"
end if
if optBtn2.State = True then
oCell = ThisComponent.Sheets(0).getCellByPosition(1,8)
oCell.String = "Yes"
end if
Putting it all together – Running the Macro
Once the form design is complete, we will show the values chosen by user in the form in the Calc cells.
To execute the form, run the StartDialog1() function. This is the same function we have used in earlier tutorials and how to kick-off/assign this function to a button’s click event – read here the first part of this tutorial series.
Select some options from the components in the form and click the button and go the LO Calc that is already open, you could see the values has read and put into the Calc Cells.
Function References – Used in this article
Complete Macro
To run, Copy and paste this entire code block below in Macro Editor in LibreOffice.
Dim oDialog1 As Object
Sub StartDialog1()
BasicLibraries.LoadLibrary("Tools")
oDialog1 = LoadDialog("Standard", "Dialog1")
lstBox1 = oDialog1.GetControl("ListBox1")
if lstBox1.getItemCount = 0 then
lstBox1.addItem("Mango",1)
lstBox1.addItem("Apple",2)
lstBox1.addItem("Orange",3)
end if
cmbBox1 = oDialog1.GetControl("ComboBox1")
if cmbBox1.getItemCount = 0 then
cmbBox1.addItem("500",1)
cmbBox1.addItem("1000",2)
cmbBox1.addItem("10000",3)
end if
oDialog1.Execute()
End Sub
Sub readDialog1()
Dim oCell
chkBox1 = oDialog1.GetControl("CheckBox1")
chkBox2 = oDialog1.GetControl("CheckBox2")
chkBox3 = oDialog1.GetControl("CheckBox3")
optBtn1 = oDialog1.GetControl("OptionButton1")
optBtn2 = oDialog1.GetControl("OptionButton2")
lstBox1 = oDialog1.GetControl("ListBox1")
cmbBox1 = oDialog1.GetControl("ComboBox1")
if chkBox1.State = 1 then
oCell = ThisComponent.Sheets(0).getCellByPosition(1,1)
oCell.String = "Debian"
end if
if chkBox2.State = 1 then
oCell = ThisComponent.Sheets(0).getCellByPosition(1,2)
oCell.String = "Ubuntu"
end if
if chkBox3.State = 1 then
oCell = ThisComponent.Sheets(0).getCellByPosition(1,3)
oCell.String = "elementary"
end if
oCell = ThisComponent.Sheets(0).getCellByPosition(1,5)
oCell.String = lstBox1.getSelectedItem()
oCell = ThisComponent.Sheets(0).getCellByPosition(1,6)
oCell.String = cmbBox1.SelectedText()
if optBtn1.State = True then
oCell = ThisComponent.Sheets(0).getCellByPosition(1,7)
oCell.String = "No"
end if
if optBtn2.State = True then
oCell = ThisComponent.Sheets(0).getCellByPosition(1,8)
oCell.String = "Yes"
end if
End Sub
Looking for Something Else?
If you are looking for something else in LibreOffice macro tutorials, Or, wants to learn more about it, please follow below link for complete Macro Tutorials Index:
