Form Processing with LibreOffice Calc Macro – Part 2

Advertisement

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

You may also like...

  • Nicola Floris

    Hello
    i looked everywhere on OO wiki and forum but i cannot find a working way to populate my ComboBox with single cells or a range, for example:
    oCell = ThisComponent.Sheets(3).getCellbyPosition(0,0)
    cmbBox1.addItem(oCell,1)
    I also tried using an array but i always says Object variable not set

    PS. thanks for the guides, very useful 🙂

    • You are very very close.

      If your cell at position 0.0 contains character, use this:
      cmbBox1.addItem(oCell.String,1)

      If your cell at position 0.0 contains numeric, use this:
      cmbBox1.addItem(oCell.Value,1)

      You need to specify LO the contents, not the entire cell.

      For more details on Cell contents type, read here:
      http://www.debugpoint.com/2015/02/deleting-all-types-of-contents-from-calc-range-using-macro/

      • Nicola Floris

        Thanks for the quick reply, now it works perfectly 🙂
        What i’m trying to do is for example, list each cell in A1:A10 from sheet3 as an option in the combo box.
        Should i use a range or specify every cell?

        • You can either use Range or individual cells – but for both, you have to run a loop for each item. Getting the data from Range would be bit complicated because Range processing in LibreOffice is complex. I would recommend to run a loop for each item and put it in combobox.

          In case you are wondering, you can get the range values using below. Range returns a 2D array with format my_data(row,col)

          my_range = ThisComponent.Sheets(0).getCellRangebyName(“A1:A10”)
          my_data = my_range.getDataArray()

          • Nicola Floris

            What do you mean loop? i just wrote this and it works:

            cmbBox1.addItem(Sheet4.getCellbyPosition(0,0).String,1)
            cmbBox1.addItem(Sheet4.getCellbyPosition(0,1).String,2)
            cmbBox1.addItem(Sheet4.getCellbyPosition(0,2).String,3)
            cmbBox1.addItem(Sheet4.getCellbyPosition(0,3).String,4)
            cmbBox1.addItem(Sheet4.getCellbyPosition(0,4).String,5)
            cmbBox1.addItem(Sheet4.getCellbyPosition(0,5).String,6)

            ………until 10

            is there another faster way? XD

            • Something like this:

              Dim i

              For i=0 to 9
              cmbBox1.addItem(Sheet4.getCellbyPosition(0,i).String,i+1)
              Next

              • Nicola Floris

                Brilliant!
                I bet that in my actual code, i am writing too simple and long 😀
                But it’s my first project ever, even if long, at least it should work

                Thank you, really

  • Uroš Grum

    Hello!
    This site has helped me before so i decided i would ask you for help aggain, i cant seem to find a property for setting the default selection in listbox, after i populate my listbox i would like to set item from the list as selected with code and i just spent an hour on the web trying to figure it out but none of the examples worked.

    • Uroš Grum

      Ok i have found one solution :
      ListBox1.selectItem(“value not index”,True) i have no idea what does the bollean value do but would still like to know how to select by index.

      • To select any item inside a ListBox by using “index” – use “selectItemPos” method. See below.

        lstBox1.selectItemPos( 0, True )

        0 = the first entry would be selected, you can count as per your need starting from 0 as first.
        True = it would be selected

        • Uroš Grum

          Thank you ! you helped me a lot, i just cant seem to be able to find a properly written example, and belive me i tried searching a lot, i think i actually found this function but example was written porly and i probably messed up and tought it wasnt right.
          Thanks aggain

  • Great Article. Thx for sharing the Macro Way under LibreOffice 🙂

Translate »