Help creating a shortcut/macro to jump between window splits

@Lupp, sorry to say no, but range names is not a solution because I would to dynamically change names of ranges (and that would be a waste of time, to be honest). Neither the Navigator is fit for this task, for the same reason.

There are two panes or window splits. If possible, I would love to jump between them using the keyboard (keybinding, I mean), remembering the last visited position, if also possible. I do not like using the mouse in this case because it breaks workflow, sort of saying. Plus, because of accessibility reasons, there should be a keybinding to jump between splits (IMHO).

[crossed]
(I couldnā€™t open your ODS. It crashed my LibreOffice. I can later report about it.)
[/crossed]

I opened it. I see there is a misunderstanding. I do not freeze the splits/panes. I use the little bold mark on the left of the windows to create the split/pane. I do not know if there is a difference in coding with each possibility. That goes beyond my knowledge.

If ou didnā€™t freeze rows and only used the horizontal splitter, it should even be more clear:
The panes do not create an alternative to which one of them a cell or range belongs. As soon as you also changed selections (with the help of the cursor keys or by dragging the mouse) the same cell or parts of the same range may be shown in both panes.


ā€œJumping between panesā€ is therefore a logically impossible thing.
If this is wrong - Iā€™m obviouisly inapt to understand the situation, and further activity here on my behalf would actually just mean wasted time.
Iā€™m out of this now.

Thanks, anyway, @Lupp, for your interest.

Just to clarify, and you can see my picture above, Iā€™m not trying to jump between ranges, but between cells, and pointing to individual cells.

(Aside, you are so lovely! Iā€™m learning Esperanto ;D ) I see your interface is not fully translated.

Breaking my promise to stay off:

About your background observations:

  • Iā€™m a German living in MĆ¼nchen (Germany).
  • I read a bit about Esperanto many years ago, but I never actually studied it.
  • Iā€™m using Windows because I needed software only working under this so-called OS (again years ago), but now Iā€™m too old to waste my time with changing to a Linux.
  • I set my LibreOffice UI to English (GB), the locale to English (Canada) and the default language for documents to Esperanto because I gravely dislike all the ā€œnationalā€ or regional nonsense. Win doesnā€™t support reasonable settings, anyway.

About your actual issue:

  • In Calc single cells also support all the services for CellRange objects.
  • Whether we talk about cells or ranges isnā€™t actually relevant here.
  • The relevant thing is -told once more- that your ranges/cells not are inside one or another pane (split part of the view). It dependsā€¦
  • For reasons explained more than once the ā€œjumping between window splitsā€ is a pipe-dream. This is demonstrated in the image I posted above.
  • Using the already suggested named ranges, you (somebody) can, of course, create user code (and assign it to KB shortcuts) speeding up the definition/re-definition of two named ranges, say A_Range and B_Range.
  • If you then also assign macros selecting one or the other of these ranges to KB shortcuts , you may have functionally (not literally) what you want.
  • You will thus need four KB shortcuts for the purpose.

You can see different rows in each window, but you canā€™t position the cursor in different rows. Anyway, this donā€™t help us here to solve the issue.
I see that there are First Page and Last Page .uno commands (also Previous and Next), but they work only in the Print Preview mode.

@Lupp Thanks for your insight!

To match your confidence, Iā€™m a Mexican living in Cuernavaca; I learned Esperanto a few years ago; and I love Linux (I switched 25 years ago, and couldnā€™t go back to Windows ;D

I visited MĆ¼nchen in 2019. Itā€™s a beautiful city, with spectacular surrounding Alps cordillera

Itā€™s so good to know we are all real people behind these alias and computers.

Itā€™s not desirable to have the same position at the same time in both panes, of course @LeroyG

ā€¦ but it may simply be unavoidable depending on what changes to the current selection you made since your cells (ranges) were defined.

(I simply donā€™t understand your insisiting.)

Play with the attachment.
disask101322reportAboutCurrentViewPanesSpecialWithMacros.ods (24.5 KB)

Gentlemen, it seems to me that there is no particular subject for debate here. :slightly_smiling_face:


There is a window divided into several panes (2 or 4 panes). Each pane shows some area (visible range of cells) of the sheet. One of the panes is active, as the ViewData property of the document controller tells us (see @Hanyaā€™s description of ViewData here).


@LobaLuna wants to create a macro that will be when called:

  1. Remember the address of the cell that is active at that moment and the index of the active pane (@Hanya: 6: index of focused window). For the index of each pane, we need to provide a separate place for storing the address of the active cell.
  2. Determine (cyclically) the number of the next pane that needs to be made active as a result of the macro (target pane).
  3. If the active cell was previously remembered for the target pane (at step 1), then set this cell as active if the cell belongs to the visible range of cells of the target pane. Otherwise, set a new active cell in the upper left corner of the target pane.


    Iā€™ll try to write such a macro today or tomorrow.
1 Like

Good luck! Donā€™t feel discouraged, please! Butā€¦
I would suppose there are (at least) three different concepts of ā€œfocusā€ here:

  • The ā€œmouse wheel focusā€: Top pane(s) or bottom pane(s).
  • The ā€œcursor keys focusā€: One of the panes.
    (May also control the effects of the ā€œbig-step-keysā€.)
  • The ā€œediting focusā€: One cell or a range(s) object taking whatā€™s typed using ordinary keys or Del (e.g.).

In addition there are up to four scroll-bar handles.
As far as these elements are coded to the ViewData, Iā€™m afraid, much of it will be ReadOnly.

1 Like

Try a second time.
Should work with 2 and 4 panes.
Comments are accepted. :slightly_smiling_face:

Option Explicit

Global aInfo    ' An array to store information about sheet panes.
                ' Element is an array of 10 elements. Indexes:
                ' 0:   RuntimeUID of the document;
                ' 1:   sheet index; 
                ' 2-9: column and row of the active cell at the time the macro is called for panes 0-3
Global indInfo As Long
Global iCurrInfo As Long

' lang:en
' Remembers the active cell and moves to the next window pane.
' If possible, the previously saved cell for the target pane is set as active.
' - oDoc  Spreadsheet document. If is missing then ThisComponent.
Sub JumpToNextPane(Optional ByVal oDoc As Object)
  Dim oCont As Object
  Dim nPanes As Long, currPane as Long, nextPane as Long, arr, arr2, aSh, i As Long
  Dim iSheet As Long, shInfo As String, nCol as Long, nRow as Long, sep As String, vRange
  If IsMissing(oDoc) Then oDoc=ThisComponent
  If Not HasUnoInterfaces(oDoc, "com.sun.star.sheet.XSpreadsheetDocument") Then Exit Sub
  oCont=oDoc.CurrentController
  If Not HasUnoInterfaces(oCont, "com.sun.star.sheet.XSpreadsheetView") Then Exit Sub
  
  nPanes=oCont.Count               ' Number of Panes
  If nPanes<2 Then Exit Sub
  
  arr=Split(oCont.ViewData, ";")   ' View Data: https://docs.libreoffice.org/sc/html/classScViewData.html#abb5889c04d6857f169f07e4fc15c03b8 
  iSheet=Clng(arr(1)) + 3          ' Active sheet index in arr
  shInfo=arr(iSheet)               ' Sheet info
  sep="+"                          ' Token separator: + or /
  arr2=Split(shInfo, sep)         
  If Ubound(arr2)=0 Then
    sep="/"
    arr2=Split(shInfo, sep)
  End If    
   
  currPane=Clng(arr2(6))
  If nPanes=4 Then                            ' Hori and Vert Split
     nextPane=(Clng(arr2(6))+1) Mod 4
  ElseIf arr2(2)="1" Then                     ' Vert    
     nextPane=IIf(currPane=2, 3, 2)
  Else                                        ' Hori
     nextPane=IIf(currPane=2, 0, 2)     
  End If
  arr2(6)=Cstr(nextPane)
  
  ' Look for information about the sheet in the aInfo array
  FindInfoIndex oDoc.RuntimeUID, Clng(arr(1))
  aSh=aInfo(indInfo)
  
  ' Save currpane
  ash(2+2*currPane)=Clng(arr2(0))
  ash(3+2*currPane)=Clng(arr2(1))
  
  nCol=-1
  If Not IsEmpty(ash(2+2*nextPane)) Then
    nCol=ash(2+2*nextPane)
    nRow=ash(3+2*nextPane)
  End If
  
  ' pane index in controller numbering.
  If nPanes=4 Then
    i=IIf(nextPane=1, 2, Iif(nextPane=2, 1, nextPane))
  Else
    i=IIf(nextPane<currPane, 0, 1)
  End If
  
  ' Check VisibleRange
  vRange=oCont.getByIndex(i).VisibleRange
  With vRange
  
    If nCol>=0 Then
      If nCol > .EndColumn Or nCol < .StartColumn Or nRow < .StartRow Or nRow > .EndRow Then  nCol=-1
    End With 
     
    If ncol=-1 Then
      nCol=.StartColumn
      nRow=.StartRow 
    End If
  End With  
  
  arr2(0)=Cstr(nCol)
  arr2(1)=Cstr(nRow)
      
  arr(iSheet)=Join(arr2, sep)
  oCont.restoreViewData Join(arr, ";")
End Sub

' Look for information about the sheet in the aInfo array.
' Assigns the iCurrInfo variable.
Sub FindInfoIndex(ByVal UID As String, ByVal n As Long)
  Dim aSh, i As Long
  If Not IsArray(aInfo) Then
    ReDim aInfo(99)
    indInfo=-1
    iCurrInfo=-1
  End If
    
  If iCurrInfo>=0 Then
    aSh=aInfo(iCurrInfo)
    If aSh(0)=UID And aSh(1)=n Then Exit Sub
  End If
  
  For i=0 To indInfo
    aSh=aInfo(i)
    If aSh(0)=UID And aSh(1)=n Then
      iCurrInfo=i
      Exit Sub
    End If
  Next i
    
  indInfo=indInfo+1
  If indInfo>Ubound(aInfo) Then
    ReDim Preserve aInfo(2 * Ubound(aInfo))
  End If  
  aSh=DimArray(9)
  aSh(0)=UID
  aSh(1)=n
  aInfo(indInfo)=aSh
  iCurrInfo=indInfo
End Sub
2 Likes

Itā€™s not that I insist out of nothing. As far as I can see, you donā€™t understand at all what I am asking for, @Lupp. Please read @sokol92 synthesis in a previous message.

It totally works as expected!

I tried with two horizontal panes, as my regular layout, and it worked perfectly. The first time it started in cell top left of each pane, which is ok; then I moved to cell g3 and jumped to the bottom pane. I moved to my desired position g200, and started to jump between panes in correct places without using the mouse.

Really, this great macro needs to be know by many in the community! Specially the guy who ask for the keybinding, as I mentioned in my first post. May I post it in the Spanish LibO blog? Or maybe you could write the article and we can put it for the whole community.

Thank you very much again!

Sure you can. LibreOffice is open source software, and the same applies to the macros posted on the forum.
By the way, I started programming in LibreOffice several years ago (after many years of working with Excel VBA) and learned a lot from Mr. Luppā€™s posts, to whom I once again want to express my gratitude.

1 Like

Wolfgang, there really are more pitfalls than it seems. :slightly_smiling_face:
Letā€™s take panel indexes for example.
In the Viewdata property:

  1. If the window is not split, then the pane has index 2.
  2. If the window is split horizontally (with a vertical line), then the indices are 2 and 3.
  3. If the window is split vertically (horizontal line), then the indices are 0 and 2.
  4. If the window is split horizontally and vertically, then the panes are numbered 0 (top left), 1 (top right), 2 (bottom left) and 3 (bottom right).

In the getByIndex method of the document controller, the same panels are numbered differently in the case of 4 panels:
0 (top left), 2 (top right), 1 (bottom left) and 3 (bottom right).
This was done on purpose so that programmers do not rest on their laurels (Iā€™m not sure about the correct translation of this idiom). :slightly_smiling_face:

Perfect for german/englishā€¦

1 Like