Macro to keep 3 rows visible under the selected row in LibreOffice Calc

Question

In Calc or MS Excel when one is finished editing a cell in a row, pressing Enter leads to the next row below. Keep doing that and eventually you arrive at the last row visible on the screen. Here when you press Enter, it will lead to the next row but now it is the last row. It is difficult to see last row on screens like Nreal Air glasses (as the bottom is always blurry). My solution was to keep the currently selected row in the middle. It means that whenever Enter is pressed a macro will check how many rows are visible under the newly selected row. If it is less than 3, the visible rows will move up (hence keeping the selected row in the middle). The following answers solve this exact problem in MS Excel. I would like to do the same in LibreOffice Calc. However, I have been unsuccessful until now to write a macro which checks the visible rows under the selected row and adds a new row every time I press Enter (hence keeping the next selected row in the middle of screen). Any idea how to do it in Calc?

score 0 · Accepted Answer · answered Nov 21 '23 at 09:28

Your task is not difficult to implement - in fact, you only need to use the .setFirstVisibleRow() method to change the display area taking into account the values of "current row" and "last visible row". For example, this macro can do this:

Sub onSelectionChanged(Optional oEvent As Variant)
Dim oCurrentController As Variant
Dim arrayOfString() As String, tmpString As String
Dim aVisibleRange As New com.sun.star.table.CellRangeAddress
Dim nSheet As Integer,  nStartRow As Long,  nEndRow As Long, nCurrentRow As Long
    oCurrentController = ThisComponent.getCurrentController()
    aVisibleRange = oCurrentController.getVisibleRange()
    nSheet = aVisibleRange.Sheet
    nStartRow = aVisibleRange.StartRow
    nEndRow = aVisibleRange.EndRow
    arrayOfString = Split(oCurrentController.getViewData(), ";")
    If UBound(arrayOfString) < (3 + nSheet) Then Exit Sub
    tmpString = arrayOfString(3 + nSheet)
    If InStr(tmpString,"+") > 0 Then
        arrayOfString() = Split(tmpString, "+")
    Else
        arrayOfString() = Split(tmpString, "/")
    EndIf
    nCurrentRow = CLng(arrayOfString(1))
    If nEndRow - nCurrentRow < 4 Then  oCurrentController.setFirstVisibleRow(nStartRow + nCurrentRow - nEndRow + 3)
End Sub

The main difficulty lies in choosing how to run this macro. For example, you can assign this script as a handler for the "Selection changed" event.

Set Event Listener

But in this case, the macro will only work for those sheets to which it is assigned. (By the way, the Excel solution you refer to in your question works exactly the same - only for the specified sheet).

You can think of some kind of general macro that when you open any spreadsheet, will assign this event listener to all sheets, and assign it to the entire office suite.

Macro to keep 3 rows visible under the selected row in LibreOffice Calc

1 Answers1