How to scroll all sheets together (or other ~3D-li

Question

I would like a "3D" spreadsheet, with the aim of having main entries in the normal 2D cells and then (potentially just-as-important) entries in a third dimension above each cell. This is what the multiple sheets already allow for, the problem is that I cannot easily see all the 3rd dimension cells at once.

I was first hoping to find some sophisticated method of using actual 3D sheets but with no luck so far. I realised that if I could only make sure the other sheets would line up with my first sheet then that would be good enough, so I could "scroll" along the third dimension by switching between sheets.

So, is there any way to scroll all the sheets together?

Such that if I scroll down on sheet 1 to see cell 123 A, when I switch to sheet 2, cell 123 A is in the same position on the screen, and so on for sheets 3 and up.

If this is impossible but anyone has suggestions for a different solution (even using a different program (on Linux)) that would be great.

Answer 1

Here is a complete working solution in OpenOffice Basic:

Global OldSheet As Object
Global HandlingActivationEvent As Boolean

Sub RegisterMyActivationEventListener
    oListener = CreateUnoListener( _
        "ActivListener_", "com.sun.star.sheet.XActivationEventListener" )
    oController = ThisComponent.CurrentController
    oController.addActivationEventListener(oListener)
    OldSheet = oController.ActiveSheet
    HandlingActivationEvent = False
    MsgBox "Now Listening"
End Sub

Sub ActivListener_activeSpreadsheetChanged( oEvent )
    If HandlingActivationEvent Then
        Exit Sub
    End If
    HandlingActivationEvent = True
    oController = ThisComponent.CurrentController
    newSheet = oController.ActiveSheet
    oController.setActiveSheet(OldSheet)

    col = oController.getFirstVisibleColumn()
    row = oController.getFirstVisibleRow()
    oController.setActiveSheet(newSheet)
    oController.setFirstVisibleColumn(col)
    oController.setFirstVisibleRow(row)

    'MsgBox col & ", " & row
    OldSheet = newSheet
    HandlingActivationEvent = False
End Sub

To make it work, go to Tools -> Customize. In the Events tab, assign RegisterMyActivationEventListener to the Open Document event.

Explanation of the code:

Instead of scrolling the sheets at the same time, it just scrolls to the appropriate cell whenever a different sheet is activated. It uses the XViewPane interface to see where the previous sheet is scrolled, and scrolls the next sheet to that same spot.

To find out when a sheet gets activated, the code uses an event listener for the XActivationEventListener interface.