Form Processing with LibreOffice Calc Macro – Part 2

4 min


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:

Toolbox in LibreOffice
Toolbox in LibreOffice

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
Designed Form

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.

Form with Values Selected
Form with Values Selected
Output
Output

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:

LibreOffice Macro Tutorial Index


We bring the latest tech, software news and stuff that matters. Stay in touch via Telegram, Twitter, YouTube, and Facebook and never miss an update!

Join our Telegram channel and stay informed on the move.

Also Read

SEE ALSO:   Files and Directory Processing Using LibreOffice Calc Macro using Basic

Like it? Share with your friends!

Arindam

Creator of debugpoint.com. All time Linux user and open-source supporter. Connect with me via Telegram, Twitter, LinkedIn, or send us an email.
Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

13 Comments
Newest
Oldest Most Voted
Inline Feedbacks
View all comments
13
0
Would love your thoughts, please comment.x
()
x