Calc Cell Selection Processing Using Macro

Advertisement

In this tutorial I will cover selection mechanism in LibreOffice Calc cells and via macro.

getCurrentSelection() Method

First we need to get hold of the Calc workbook that is open. To do that, you can use ThisComponent and hold the return object in a variable.

To find out the selections done by user in a Calc sheet, we will use the getCurrentSelection() method of Xcomponent interface. This method “provides read access on current selection on controller”. It returns the current selection on the current controller.

Dim oDoc, oSel
oDoc = ThisComponent
oSel = oDoc.getCurrentSelection()
If there are no selection, getCurrentSelection returns NULL. You can use IsNull function to determine whether anything is selected or not.

By using only oSel, it is not possible to determine the selections. We need to take help of the method supportService of XServiceInfo interface.

supportService method “tests whether the specified service is supported, i.e. implemented by the implementation”. If its supported, it returns TRUE, else it returns FALSE.

There are list of services available that can be tested using this function. For this tutorial we will use below services:

  • com.sun.star.sheet.SheetCell
  • com.sun.star.sheet.SheetCellRange
  • com.sun.star.sheet.SheetCellRanges

An entire list of service can be found here [LibreOffice Reference]:
com.sun.star.sheet

One cell is selected

To check whether one cell is selected, use com.sun.star.sheet.SheetCell service. You can also get the value of that selected cell by using getString() method of selection object.

If oSel.supportsService("com.sun.star.sheet.SheetCell") Then
	MsgBox "One Cell selected and it contains: " & oSel.getString()
End If

One range is selected

To identify a range of cells selection – say, C5:F7, use the service com.sun.star.sheet.SheetCellRange.

If oSel.supportsService("com.sun.star.sheet.SheetCellrange") Then
	MsgBox "One Cell Range selected"
End If

Multiple ranges are selected

To identify multiple ranges of cells selection, use the service com.sun.star.sheet.SheetCellRanges.

If oSel.supportsService("com.sun.star.sheet.SheetCellRanges") Then
	MsgBox "Multiple Cell Ranges selected. Total=" & oSel.getCount()
End If

Running the Macro

Single Selection

Output

Single Range Selection

Output

Multiple Ranges Selection

Output

Complete Macro

Sub Main

	Dim oDoc, oSel

	oDoc = ThisComponent
	oSel = oDoc.getCurrentSelection()
	
	If oSel.supportsService("com.sun.star.sheet.SheetCell") Then
		MsgBox "One Cell selected and it contains: " & oSel.getString()
	Else
		If oSel.supportsService("com.sun.star.sheet.SheetCellRange") Then
		    MsgBox "One Cell Range selected"
		Else
		    If oSel.supportsService("com.sun.star.sheet.SheetCellRanges") Then
				Msgbox "Multiple Cell Ranges selected. Total=" & oSel.getCount()
		    Else
			    Print "Somethine else is selected."
			End If
		End If
	End If	
	
End Sub

Function References – Used in this article

 

Looking for Something Else?

If you are looking for something else in LibreOffice macro tutorials, Or, wants to learn more about it, please follow below link for complete Macro Tutorials Index:

LibreOffice Macro Tutorial Index

You may also like...

Translate »