In this tutorial I will cover selection mechanism in LibreOffice Calc cells and via macro.
Table of Contents
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:
