Many times, it is needed to have a quick button on the Calc sheet itself and perform some tasks or run a macro when the button is clicked. This tutorial would show how to do that.
Table of Contents
Objective
Add a button on top of CALC sheet. Create a dialog with a textbox and a button. When the dialog’s button is clicked, we would send the text from the textbox to inside a cell of Calc sheet.
Create a Dialog
Create a dialog with a TextField and CommandButton control. Open Organize Dialogs from Tools -> Macros. Click NEW to create a dialog and then click Edit to enter design mode.
Drag a TextField and Command Button control to the dialog. The design may look like this:
Add a Push Button to Calc
Adding a push button on the Calc sheet is bit different from adding it in a dialog. Open the Form Control toolbar from View -> Toolbars -> Form Controls
Click on the Button icon in Form Controls box and drag and draw a button in Calc sheet.
The Macro
We need to write two functions. First function – start_form() would start the dialog we have designed. Second function – send_to_calc() would take the text from the dialog and put it to a cell (say B2) in Calc.
start_form() function is very straightforward. We need to define a variable to hold the dialog object and execute it. [Read more about Dialog control here].
Sub start_form()
oDialog1 = CreateUnoDialog(DialogLibraries.Standard.Dialog1)
oDialog1.Execute()
End Sub
send_to_calc() function is also very simple. Need to hold the text entered in textfield control and put it into Cell B2 (i.e. col=1, row=1) in Calc Sheet.
Sub send_to_calc()
Dim myText As String
myText = oDialog1.getControl("TextField1").getText()
ThisComponent.Sheets(0).getCellByPosition(1,1).String = myText
End Sub
Assigning Events to Push Button
The designed dialog needs to open up when the push button is clicked on Calc sheet. We need to trigger/run the start_form() macro via push button. To do that, click the push button so that it is selected. On the Form Control Dialog, click control Properties icon. In the next window, select Events tab. Click the small button against ‘Execute Action’ event.
Click the Macro button in next Assign Action window and select the function start_form(). Once you have selected, the event window should look like this.
This should open up the dialog when the button is clicked.
Assigning Events to Dialog Button
Now we need to assign the send_to_calc() function to the actual dialog button, so that, the text from the text field can be sent to a cell. To do that, go to macro editor and select the design mode of the dialog. Select the button and click Events tab. Against execute action – similarly select the function send_to_calc(). Once you done that, click OK and your properties window should look like this.
Running the Macro
Its time to run the macro. Before we run, we need to turn off the design mode in Calc where the button is placed. Click the button ‘Toggle Design Mode’ in form control window. It will turn off the design mode and the button would be ready to receive user actions.
After design mode is off, click the button in Calc sheet. It should open up the dialog. Then type anything you want in the dialog and click send to Calc button. You can see the text have been put to the Cell 1,1 i.e. B2.
Here is a GIF of the output. You can find the entire macro at the end of the article.
Output
Complete Macro
Dim oDialog1 As Object
Sub start_form()
oDialog1 = CreateUnoDialog(DialogLibraries.Standard.Dialog1)
oDialog1.Execute()
End Sub
Sub send_to_calc()
Dim myText As String
myText = oDialog1.getControl("TextField1").getText()
ThisComponent.Sheets(0).getCellByPosition(1,1).String = myText
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:
