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

Advertisement

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.

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

You may also like...

  • Arthur Szász

    Thank you for this compilation… it was useful.

    it is worth mentioning that when I copy-pasted the bellow code and got a message with sintax error

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

    because of the quotation marks arround the word “Tools”
    (they are opening and closing quotation marks, notice the difference against “Standard” and “Dialog1”)

    • That’s a good catch. The article is updated. Thanks.

  • Uroš Grum

    I cant run this code … im really frustrated with libre office as i have been trying since friday to do something that would take me litteraly 10 minutes in excel. But back to my problem :

    “oT1 = Dialog.GetControl(“TextField1″)”

    at the upper part of code i get object variable not set, error i have everything the same, i even tried to change the name of said control but no difference. Anny sugestions ?

    • I am assuming that you are using the same example I had here.

      In my example the dialog object name is “oDialog1”. In your statement you have mentioned “Dialog”. You should be using the dialog object name that you defined in GetControl.

      oT1 = oDialog1.GetControl(“TextField1”)

      • Uroš Grum

        Thank you for reply, the code is corect, i just changed that when i was trying to get rid of error and then i forgot to change that back … its not the problem becouse at the time i had defined it as Dialog … here is my entire code :

        Dim oDialog As Object

        Sub StartDialogA()

        DialogLibraries.LoadLibrary(“Standard”)

        oDialog = CreateUnoDialog(DialogLibraries.Standard.TesniDialog)

        oDialog.Execute()

        End Sub

        Sub ReadDialogA()

        Dim cellValue

        oT1 = oDialog.GetControl(“TextField1”)

        cellValue = ThisComponent.Sheets(0).getCellByPosition(0,0)

        oT1.Text = “This”

        End Sub

        ps. The start dialog works fine

      • Uroš Grum

        I have no clue why …. but suddenly it started to work … iddnt change anny code ….. the libre office crashes a lot and even crashes every single time i close the program , maybe becouse of windows ?
        ah well at least now works , thanks 🙂

        • Maybe. But make sure you have the latest LibreOffice build and updates are applied as well. Glad to know it worked!

  • Ricardo Gunawan

    Hi, I would ask a question.
    If the Dialog1 and Module1 as you create above is in Untitled1 branch, why show this error ??

    • I am really not sure why you get that error, I didn’t get. Did you execute the StartDialog() function? This working example tested in Ubuntu/Linux only. It seems you are using Windows which may cause this issue. Why don’t you try the alternate method of creating the dialog as mentioned in the article also below. Keep me posted.

      DialogLibraries.LoadLibrary(“Standard”)
      oDialog1 = CreateUnoDialog(DialogLibraries.Standard.Dialog1)
      oDialog1.Execute()

      • Ricardo Gunawan

        Ah yes…. perfect!! Problem solved with the alternate method.
        Thanks

    • Hüseyin

      Hi;
      I have done to show diaglog page on Calc. Please look at below code to help your mind.
      DialogLibraries.LoadLibrary(“VBAProject”)
      oDialog1 = CreateUnoDialog(DialogLibraries.VBAProject.Dialog1)
      oDialog1.Execute()

  • Robin Hahn

    Good start, indeed. I’m glad someone is doing this. BTW, the process creating an object out of scope, then instantiating and the reading reminds me a bit of Gambas. Going to have a look at your other tutorials. Then, I’m going to try and start converting some of my VBA stuff.

    • @disqus_nivOZz4jZT:disqus Thanks. The tutorials is the result of my own trial-and-error method of working content. There are not much content available in the web today, to make things as standard. But still, every article I publish is an ongoing process and I would update if I find a better way to do any macro problem.

      • Christoph S

        Thanks a lot for this great tutorials, this is exactly what I was searching for!

Translate »