Jump to content

Macros/ScriptForge/WaitforUserInputQuickwin

From The Document Foundation Wiki


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.

  1. Open the Basic IDE
  2. Select the first available blank module
  3. Copy and paste the Basic code
  4. Run Main()

How to run it in Python

Create a new document.

  1. Run APSO
  2. Create a new module, call it 'Module1'
  3. Copy and paste the Python code below
  4. 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,)

See also