Working with Dialog Controls in LibreOffice Calc using Macro – Part 1

4 min


This tutorial will cover on how to create a basic LibreOffice Dialog and adding various controls such as TextBox. Also it covers on how to read the control values using Calc Macros.

Adding a Dialog

To add a Dialog in LibreOffice Calc, select from menu: Tools -> Macros -> Organize Dialogs…

Create Dialog from Menu
Create Dialog from Menu

Once above options is chosen, below LO Basic Macro Organizer would open. On the Dialog Tab, click New button.

Create a New Dialog
Create a New Dialog

On the New Dialog pop-up , give a name to your dialog. For this tutorial, lets keep it as default Dialog1. Press OK.

New Dialog
New Dialog

You can see the Dialog1 is come up under My Dialogs -> Standard -> Dialog1.

Adding a TextBox Control

On the same LO Basic Macro Organizer, click EDIT while Dialog1 is selected.

Edit Dialog
Edit Dialog

Entire Dialog editor would open inside LO Calc. Now its time to add some controls. For simplicity, I would add a textbox and a button.

The controls are placed at the bottom of the screen. Select a control and drag your mouse inside the Dialog to place your desired control. For this tutorial, add a textbox and a button. If you are unable to see the toolbox at the bottom of the screen, select from menu: View -> Toolbars -> Toolbox.

The Dialog should look like this now.

The Dialog
The Dialog

To set the properties of each controls, click/select the control, then you can see the properties window opened on the bottom-left side of the editor. On the properties window, the ‘name’ value of the General tab would be used to identify the objects inside Macro.
For textbox the default name is: TextField1, for the button it is: CommandButton1.

Dialog with Properties
Dialog with Properties

Now, we are all set with the Dialog, for now. Lets write the macro.

The Macro

Using a LO Calc macro, I will show how to open the dialog, read the contents of the textbox and some content from Calc cell from sheet1. Then show the content of the textbox and contents from Cell as a message box prompt while the button is clicked.

Go to the macro editor (click the Module1 tab) and create two functions as below.
Tip: Read the tutorial on how to create a Macro

Dim oDialog1 As Object

Sub StartDialog1()
    ' Code for initiating and showing the dialog
End Sub

Sub readDialog1()
    ' Read the content 
End Sub

Declare an object oDialog1 as Object type outside of the functions scope. Because we need to access the Dialog in a difference function.

In StartDialog1() function we will load the “Tools” library. As per current design of OpenOffice/LibreOffice, the libraries are implemented as UNO interfaces, NOT as UNO Services. When OpenOffice/ LibreOffice is initiated, not all the basic libraries are loaded to save time. Thus if you have created your own library say, Library1, you need to load the library explicitly inside basic to identify the user defined functions. To load any library, use the built-in properties BasicLibraries.LoadLibrary.

SEE ALSO:   Writing a Macro in LibreOffice Calc - Getting Started

For this tutorial, we will use the OpenOffice/LibreOffice predefined library “Tools” with below code. LoadLibrary property will load the Tools library and then using LoadDialog() function and Execute function we will show the created Dialog.

Sub StartDialog1()
    BasicLibraries.LoadLibrary("Tools")
    oDialog1 = LoadDialog("Standard", "Dialog1")
    oDialog1.Execute()
End Sub

Alternate Code
Using below snippets also, a dialog can be opened.

    DialogLibraries.LoadLibrary("Standard")
    oDialog = CreateUnoDialog(DialogLibraries.Standard.Dialog1)

Now its time to read the content of TextBox and 1st cell of Sheet1 of Calc i.e. A1. To get a hold of a control, use GetControl( controlName ) function of the dialog object. Once the object is ready, you can use various properties available. To get the text entered in the textbox, use control’s Text() property.

Sub readDialog1()
    Dim cell_val
    oT1 = oDialog1.GetControl("TextField1")
    cell_val = ThisComponent.Sheets(0).getCellByPosition(0,0)
    msgbox cell_val.String & chr(13) & "Value from controls: "  & oT1.Text
End Sub

Before we run the macro, we need to assign the readDialog1() function to button click event. To do that, go back to Dialog Editor and select the commandbutton. In the properties window, select Events tab. Assign the readDialog1() function by clicking the Execute Action button and choosing the macro.

Tip: Click here to learn more on how to assign a macro to a form control / button.

Run

Put any text in Calc sheet1’s cell A1 – for this tutorial, I have put “Hello World”.

Open the Macro editor and run the function StartDialog1(). The dialog would execute and popup. Now, put some text on the text box and click the button. You should see the text from the textbox from the dialog as well as from the Calc sheet1’s A1 cell.

Running the Macro
Running the Macro

This way, you can work with dialogs and controls via basic Macro in OpenOffice/ LibreOffice Calc.

Video

Watch the video of this tutorial.

Function References – Used in this article

var = Dialog.GetControl(“ControlName”) : Returns handle to the control object
TextBoxControl.Text() : Returns Textbox value [String]
BasicLibraries.LoadLibrary(“LibraryName”)
var = LoadDialog(“LibraryName“, “DialogName“) : Returns handle to the dialog.
DialogHandle.Execute()

Complete Macro

To run, Copy and paste this entire code block below in Macro Editor of LibreOffice.

Dim oDialog1 As Object

Sub StartDialog1()
    BasicLibraries.LoadLibrary("Tools")
    oDialog1 = LoadDialog("Standard", "Dialog1")
    oDialog1.Execute()
End Sub

Sub readDialog1()
    Dim cell_val
    oT1 = oDialog1.GetControl("TextField1")
    cell_val = ThisComponent.Sheets(0).getCellByPosition(0,0)
    msgbox cell_val.String & chr(13) & "Value from controls: "  & oT1.Text
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


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.

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