Jump to content

Macros/ScriptForge/PopupMenuExample

From The Document Foundation Wiki


How to simulate a Calc context menu with a popup menu

Authored by Jean-Pierre Ledure.

Display a custom popup menu when right-clicking inside a given cell of a spreadsheet. When clicking outside that area, the usual context menu items are displayed.

The technique is illustrated with a simple menu. Clicking on an item enters the clicked value in the cell, or applies the chosen cell style.

The advantages of this technique are:

  • No need of individual Subs or defs for each menu item
  • The menu may be built dynamically with sheet or database data: the example illustrates a list of actual styles
  • Checkboxes and radio buttons are supported
A popup menu to simulate a Calc context menu.
A popup menu to simulate a Calc context menu.

How to run it in BASIC

  1. Create a new document
  2. Open the Basic IDE
  3. Select the first available blank module
  4. Copy and paste the Basic code
  5. Configure in line 12 the range where the custom menu should operate
  6. Define the macro to be called when a right-click event occurs (right-click on the sheet tab + Sheet Events...) by referring to the OnRightClickPopup() sub.

How to run it in Python

  1. Create a new document
  2. Run APSO
  3. Create a new module, call it 'Module1'
  4. Copy and paste the Python code below
  5. Configure in line 16 the range where the custom menu should operate
  6. Define the macro to be called when a right-click event occurs (right-click on the sheet tab + Sheet Events...) by referring to the OnRightClickPopup() def.

Code

REM  SCRIPTFORGE WIKI EXAMPLE
REM 	How to manage popup menus in a Calc sheet ?
REM		Minimal required version: LibreOffice 25.8
REM 	Used services
REM 		SFWidgets.PopupMenu, SFDocuments.Calc

Option Explicit

'*******************************************************************
'***	Modify the range where a custom popup menu should be run
'*******************************************************************
Const targetrange = "Sheet2.C6:C25"

'*******************************************************************
'***	Associate the Right Click sheet event of the sheet
'***	with the Function below
'*******************************************************************
Function OnRightClickPopup(Optional XRange) As Boolean
'	XRange is a com.sun.star.table.XCellRange UNO object

Dim calc			As Object		'	The SFDocumets.Calc actual instance
Dim menu			As Object		'	A SFWidgets.PopupManu instance
Dim range			As String		'	The selected range, as a string
Dim intarget		As Boolean		'	When True, the right click is inside the targeted cell range
Dim cellposition	As Object		'	A com.sun.star.awt.Rectangle object
Dim cellstyles		As Variant		'	Array of style names
Dim style			As String		'	A single cell style
Dim answer			As String		'	The selected item in the menu

	GlobalScope.BasicLibraries.loadLibrary("ScriptForge")
	Set calc = CreateScriptService("Calc", ThisComponent)
	range = XRange.AbsoluteName
	
	'	Determine if the selected cell is inside the targeted range
	intarget =  ( Len(calc.Intersect(targetrange, range)) > 0 )
	'	Execute the popup menu and interpret its return value
	If intarget Then
		calc.CurrentSelection = range
		Set cellposition = calc.XRectangle(range)
		'	Build the popup menu in the center of the selected cell
		Set menu = CreateScriptService("PopupMenu", , _
						cellposition.X + cellposition.Width / 2, cellposition.Y + cellposition.Height / 2)
		menu.AddItem("Absent", Name := "A")
		menu.AddItem("Present", Name := "P")
		menu.AddItem("Excused", Name := "E")
		menu.AddItem("Unknown", Name := "?")
		menu.AddItem("---")
		cellstyles = calc.Styles(Family := "CellStyles")
		For Each style in cellstyles
			menu.AddItem("Styles>" & style)
		Next style
		'	Run the popup menu and get the user's choice
		answer = menu.Execute(False)		'	False returns the choice by its name
		Select Case Len(answer)
			Case 1		'	A single character (A, P, E or ?)
				calc.SetValue(range, answer)
			Case > 1	'	The style to apply
				calc.SetCellStyle(range, answer)
			Case Else	'	Do nothing
		End Select
		menu.Dispose()
	End If
	'	Must the standard context menu be activated ?
	OnRightClickPopup = intarget
	calc.Dispose()
End Function
# coding: utf-8
from __future__ import unicode_literals

from scriptforge import CreateScriptService
basic = CreateScriptService('Basic')

###	SCRIPTFORGE WIKI EXAMPLE
###		How to manage popup menus in a Calc sheet ?
###			Minimal required version: LibreOffice 25.8
### 	Used services
### 		SFWidgets.PopupMenu, SFDocuments.Calc, SFScriptForge.Basic

# *******************************************************************
# ***	Modify the range where a custom popup menu should be run
# *******************************************************************
targetrange = 'Sheet2.C6:C25'


# *******************************************************************
# ***	Associate the Right Click sheet event of the sheet
# ***	with the def below
# *******************************************************************
def OnRightClickPopup(XRange = None):
	# 	XRange is a com.sun.star.table.XCellRange UNO object

	# calc							The SFDocumets.Calc actual instance
	# menu							A SFWidgets.PopupManu instance
	# range							The selected range, as a string
	# intarget						When True, the right click is inside the targeted cell range
	# cellposition					A com.sun.star.awt.Rectangle object
	# cellstyles					Array of style names
	# style							A single cell style
	# answer						The selected item in the menu

	calc = CreateScriptService('Calc', basic.ThisComponent)
	range = XRange.AbsoluteName

	# 	Determine if the selected cell is inside the targeted range
	intarget = (len(calc.Intersect(targetrange, range)) > 0)
	# 	Execute the popup menu and interpret its return value
	if intarget:
		cellposition = calc.XRectangle(range)
		calc.CurrentSelection = range
		# 	Build the popup menu in the center of the selected cell
		menu = CreateScriptService('PopupMenu',
									x = cellposition.X + cellposition.Width / 2,
									y = cellposition.Y + cellposition.Height / 2)
		menu.AddItem('Absent', name = 'A')
		menu.AddItem('Present', name = 'P')
		menu.AddItem('Excused', name = 'E')
		menu.AddItem('Unknown', name = '?')
		menu.AddItem('---')
		cellstyles = calc.Styles(family = 'CellStyles')
		for style in cellstyles:
			menu.AddItem('Styles>' + style)
		# 	Run the popup menu and get the user's choice
		answer = menu.Execute(False)  # False returns the choice by its name
		if len(answer) == 1:  # A single character (A, P, E or ?)
			calc.SetValue(range, answer)
		elif len(answer) > 1:  # The style to apply
			calc.SetCellStyle(range, answer)
		else:  # Do nothing
			pass
		menu.Dispose()

	calc.Dispose()
	# 	Must the standard context menu be activated ?
	return intarget


g_exportedScripts = (OnRightClickPopup,)


See also