Macros/ScriptForge/WaitforUserInputQuickwin
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
How to wait for user input in a spreadsheet cell
Authored by Jean-Pierre Ledure.
The loading of the ScriptForge Basic library can be done elsewhere. In Basic, all used variables are declared explicitly. The concerned code is presented inside a Basic Sub or a Python def.
Run that piece of code and consider the result.
Simpler than by using listeners, the Basic and Python languages have statements to make the execution of a program waiting for the occurrence of some event. The example illustrates the feature with a very simple example: the program waits until the user enters a value in a specific cell.
The BASIC Wait
statement works smoothly in this context. While, at the opposite, the time.sleep()
method freezes LibreOffice's user interface, preventing the user from entering anything.
As an alternative, the BASIC service proposes to execute a Wait
statement in the middle of Python code.
How to run it in BASIC
Create a new document.
- Open the Basic IDE
- Select the first available blank module
- Copy and paste the Basic code
- Run Main()
How to run it in Python
Create a new document.
- Run APSO
- Create a new module, call it 'Module1'
- Copy and paste the Python code below
- Save and run the Main() method
Code
REM How to wait until an event occurs ?
REM Minimal required version: LibreOffice 7.6
REM Used service(s) Calc
Sub WaitEvent()
' How to wait a given cell receives a value ?
' Quick and dirty. Better use the "Content changed" sheet event.
Dim ui As Object
Dim calc As Object
Dim stopwait As Boolean
Dim cellValue As Variant
GlobalScope.BasicLibraries.loadLibrary("ScriptForge")
Const waittime = 500 ' (milliseconds)
Const textcell = "A10"
Const cell = "D10"
Set ui = CreateScriptService("UI")
Set calc = ui.CreateDocument("Calc")
calc.SetValue(textcell, "Enter a value in cell " & cell)
calc.CurrentSelection = cell
stopwait = False
Do While Not stopwait
cellvalue = calc.GetValue(cell)
If cellvalue <> "" Then stopwait = True Else Wait waittime
Loop
MsgBox "Go ahead with value " & cellvalue
End Sub
# coding: utf-8
from __future__ import unicode_literals
from scriptforge import CreateScriptService
basic = CreateScriptService('Basic')
### How to wait until an event occurs ?
### Minimal required version: LibreOffice 25.8
### Used service(s) Document, Calc, Basic
def waitevent():
# How to wait a given cell receives a value ?
# Quick and dirty. Better use the 'Content changed' sheet event.
waittime = 0.500 # (seconds)
textcell = 'A10'
cell = 'D10'
ui = CreateScriptService('UI')
calc = ui.CreateDocument('Calc')
calc.SetValue(textcell, 'Enter a value in cell ' + cell)
calc.CurrentSelection = cell
stopwait = False
while stopwait is False:
cellvalue = calc.GetValue(cell)
if cellvalue != '':
stopwait = True
else:
basic.Wait(1000 * waittime)
basic.MsgBox('Go ahead with value ' + str(cellvalue))
g_exportedScripts = (waitevent,)