Jump to content

Macros/ScriptForge/CustomContextMenuExample

From The Document Foundation Wiki


How to make a custom context menu in a Calc sheet

Authored by Jean-Pierre Ledure.

Display a custom context menu when the right-click is done inside a given range of a spreadsheet. When clicked 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.

A popup menu to simulate a Calc range context menu.
A popup menu to simulate a Calc range 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. Verify lines 12
  6. Configure in line 21 the range where the custom menu should operate
  7. Define the macro to be called when a right-click event occurs (right-click on the sheet tab + Sheet Events...) by referring to the OnRightClick() 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. Verify lines 16
  6. Configure in line 25 the range where the custom menu should operate
  7. Define the macro to be called when a right-click event occurs (right-click on the sheet tab + Sheet Events...) by referring to the OnRightClick() def.

Code

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

Option Explicit

'*******************************************************************
'***	Adjust module name if not "Module1"
'*******************************************************************
Const module = "Module1"
	Const scriptA = "vnd.sun.star.script:Standard." & module & ".EnterA?language=Basic&location=document"
	Const scriptB = "vnd.sun.star.script:Standard." & module & ".EnterB?language=Basic&location=document"
	Const scriptC = "vnd.sun.star.script:Standard." & module & ".EnterC?language=Basic&location=document"
	Const scriptD = "vnd.sun.star.script:Standard." & module & ".EnterD?language=Basic&location=document"

'*******************************************************************
'***	Modify the range where a custom context menu should be active
'*******************************************************************
Const targetrange = "Sheet1.C6:C25"

'*******************************************************************
'***	Associate the Right Click sheet event of the sheet
'***	with the Function below
'*******************************************************************
Function OnRightClickContext(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.ContextManu instance
Dim intarget		As Boolean		'	When True, the right click is inside the targeted cell range
	GlobalScope.BasicLibraries.loadLibrary("ScriptForge")
	Set calc = CreateScriptService("Calc", ThisComponent)
	Set menu = calc.ContextMenus("cell")
	'	Erase all items in the document's context menu
	menu.RemoveAllItems()
	
	'	Determine if the selected cell is inside the targeted range
	intarget =  ( Len(calc.Intersect(targetrange, XRange.AbsoluteName)) > 0 )
	'	Associate each menu item with its routine
	If intarget Then
		menu.AddItem("A", Script := scriptA)
		menu.AddItem("B", Script := scriptB)
		menu.AddItem("C", Script := scriptC)
		menu.AddItem("D", Script := scriptD)
	End If
	'	Activate the custom menu only when inside target, otherwise let the standard menu operate normally
	menu.Activate(intarget)
	menu.Dispose()
	calc.Dispose()
	OnRightClickContext = False
End Function

'*******************************************************************
'***	Simple actions triggered by the custom context menu
'***	Context menu items do not pass any argument
'*******************************************************************
Sub EnterA
	EnterValue("A")
End Sub
Sub EnterB
	EnterValue("B")
End Sub
Sub EnterC
	EnterValue("C")
End Sub
Sub EnterD
	EnterValue("D")
End Sub

Sub EnterValue(value)
Dim calc			As Object		'	The SFDocumets.Calc actual instance
	Set calc = CreateScriptService("Calc", ThisComponent)
	calc.SetValue(calc.CurrentSelection, value)
	calc.Dispose()
End Sub
# coding: utf-8
from __future__ import unicode_literals

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

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

# *******************************************************************
# ***	Adjust module name if not 'Module1'
# *******************************************************************
module = 'Module1'
scriptA = 'vnd.sun.star.script:' + module + '.py$EnterA?language=Python&location=document'
scriptB = 'vnd.sun.star.script:' + module + '.py$EnterB?language=Python&location=document'
scriptC = 'vnd.sun.star.script:' + module + '.py$EnterC?language=Python&location=document'
scriptD = 'vnd.sun.star.script:' + module + '.py$EnterD?language=Python&location=document'

# *******************************************************************
# ***	Modify the range where a custom context menu should be active
# *******************************************************************
targetrange = 'Sheet1.C6:C25'


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

	# calc			The SFDocumets.Calc actual instance
	# menu			A SFWidgets.ContextManu instance
	# intarget		When True, the right click is inside the targeted cell range
	calc = CreateScriptService('Calc', basic.ThisComponent)
	menu = calc.ContextMenus('cell')
	# 	Erase all items in the document's context menu
	menu.RemoveAllItems()

	# 	Determine if the selected cell is inside the targeted range
	intarget = (len(calc.Intersect(targetrange, XRange.AbsoluteName)) > 0)
	# 	Associate each menu item with its routine
	if intarget:
		menu.AddItem('A', script = scriptA)
		menu.AddItem('B', script = scriptB)
		menu.AddItem('C', script = scriptC)
		menu.AddItem('D', script = scriptD)
	# 	Activate the custom menu only when inside target, otherwise let the standard menu operate normally
	menu.Activate(intarget)
	menu.Dispose()
	calc.Dispose()
	return False


# *******************************************************************
# ***	Simple actions triggered by the custom context menu
# ***	Context menu items do not pass any argument
# *******************************************************************
def EnterA():
	EnterValue('A')
def EnterB():
	EnterValue('B')
def EnterC():
	EnterValue('C')
def EnterD():
	EnterValue('D')
	
def EnterValue(value):
	# calc				The SFDocumets.Calc actual instance
	calc = CreateScriptService('Calc', basic.ThisComponent)
	calc.SetValue(calc.CurrentSelection, value)
	calc.Dispose()


g_exportedScripts = (OnRightClick,)


See also