Macros/ScriptForge/PopupMenuExample
Appearance
< Macros | ScriptForge
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
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
ordefs
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

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
- Configure in line 12 the range where the custom menu should operate
- 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
- Create a new document
- Run APSO
- Create a new module, call it 'Module1'
- Copy and paste the Python code below
- Configure in line 16 the range where the custom menu should operate
- 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,)