Get Row and Column number from "ThisComponent.CurrentSelection"

[Fabiotk]

I have this code where I can get which one is the current selected cell and use it to modify its value:

theSelection = ThisComponent.CurrentSelection
theSelection.setString("some value")

Now I want to move to the next column to the right, if it was Microsoft excel VBA I could just use something like theSelection.Offset(0,1) but that’s not the case. So I’m doing some workarounds of course:

nextCell = oActiveSheet.getCellByPosition( ???currentColumn + 1, ???currentRow)
ThisComponent.CurrentController.select( nextCell )

I just want to know the simplest way to replace these ??? to the actual values of the theSelection var to move to the next column to the right.

I also tried this:

nextCell = oActiveSheet.getCellByPosition( column() + 1, row())

But I don’t know why it is always returning column() = 1 and row() = 1 in regardless of which is the value of the CurrentSelection. Thanks in advance for the help.

Edit: I’m using libreoffice version 6.3.6.2 just in case it makes difference

[jimk]

Cross-posted to Get Row and Column number from "ThisComponent.CurrentSelection" in libreoffice calc basic - Stack Overflow.

[JohnSUN]

First, let’s figure out what you get from the CurrentSelection method, this is important.

Calc returns a single cell, a range of cells, or an array of several ranges. The third option is possible when you hold down the Ctrl key and select several non-contiguous cell ranges.

I intentionally don’t mention the case where the current selection is a drawing shape, a control like a button or checkbox, a chart, or something else that may be present on a sheet of your spreadsheet. The task is not so simple, let’s not complicate it.

What should be the behavior of the macro in the first case is clear - you just need to get the next cell to the right of the selected one.

  Dim aCellAddress As New com.sun.star.table.CellAddress
  oCurrentSelection = ThisComponent.getCurrentSelection()
  If oCurrentSelection.supportsService("com.sun.star.sheet.SheetCell") Then 
  	aCellAddress = oCurrentSelection.getCellAddress()
  	oNextCell = oCurrentSelection.getSpreadsheet().getCellByPosition(aCellAddress.Column + 1, aCellAddress.Row)
  End If

It’s just as long as the cell is not in the last column - for your version of Calc this is the AMJ column. For a macro to work well, you must handle this situation.

 If oCurrentSelection.getSpreadsheet().getColumns().getCount()-1 > aCellAddress.Column Then
	  oNextCell = oCurrentSelection.getSpreadsheet().getCellByPosition(aCellAddress.Column + 1, aCellAddress.Row)
 Else
	  MsgBox "There is no cell to the right of the current selection", MB_ICONSTOP, "Wrong selection"
	  Exit Sub
 EndIf

This is approximately how your task is solved for one selected cell.
With a range or set of ranges, the task becomes much more complicated. You can try to handle these situations, but it’s easier to ignore them. In this case, the full code looks like this:

Dim aCellAddress As New com.sun.star.table.CellAddress
  oCurrentSelection = ThisComponent.getCurrentSelection()
  If oCurrentSelection.supportsService("com.sun.star.sheet.SheetCell") Then 
    aCellAddress = oCurrentSelection.getCellAddress()
    If oCurrentSelection.getSpreadsheet().getColumns().getCount()-1 > aCellAddress.Column Then
      oNextCell = oCurrentSelection.getSpreadsheet().getCellByPosition(aCellAddress.Column + 1, aCellAddress.Row)
    Else
      MsgBox "There is no cell to the right of the current selection", MB_ICONSTOP, "Wrong selection"
      Exit Sub
    EndIf
  Else
    MsgBox "Please select only one single cell", MB_ICONSTOP, "Wrong selection"
    Exit Sub
  End If
  oNextCell.setStrinf("Next value")