Scroll a specific cell to the top-left corner of the sheet

Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This section is not for asking questions about writing your own macros.
Post Reply
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Scroll a specific cell to the top-left corner of the sheet

Post by Lupp »

To address the specific cell in RC syntax (only absolute so far) should be appropriate.
The following code will neither change the selection nor the focus-cell ('ActiveCell' in VBA).

Code: Select all

Sub scrollCellByRCtoTopLeft(Optional pCellByRC)
If isMissing(pCellByRC) Then pCellByRC = "R15C3"
REM Remove the example if no longer useful!
h = InStr(pCellByRC, "C")
r = 0 + Mid(pCellByRC, 2, h - 2) - 1
c = 0 + Right(pCellByRC, Len(pCellByRC) - h) - 1
doc = ThisComponent
cCtrl = doc.CurrentController
cCtrl.FirstVisibleRow = r
cCtrl.FirstVisibleColumn = c
End Sub
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Scroll a specific cell to the top-left corner of the she

Post by Villeroy »

Handle split/frozen window, avoid string address:

Code: Select all

oView = ThisComponent.getCurrentController()
oSheets = ThisCOmponent.getSheets()
addr = oCell.getCellAddress()
oView.setActiveSheet(oSheets.getByIndex(addr.Sheet))
iLastPane = oView.getCount() -1
oPane = oView.getByIndex(iLastPane)
oPane.setFirstVisibleRow(addr.Row)
oPane.setFirstVisibleColumn(addr.Column)
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: Scroll a specific cell to the top-left corner of the she

Post by Lupp »

Sorry. I cannot see the point.
Of course, the routine I posted can easily be reworked to accept the cell as an object or given by column and row separately. I chose the RC reference as a string for (supposd) convenience.

Code: Select all

Sub scrollCellToTopLeft(pCell)
doc = ThisComponent
REM Since the cell doesn't give direct access to the documnet.
REM Workaround (by Villeroy) available.
cCtrl = doc.CurrentController
With pCell.CellAddress
cCtrl.FirstVisibleRow = .Row
cCtrl.FirstVisibleColumn = .Column
End With
End Sub
Why dive down to the pane?
On Windows 10: LibreOffice 24.2 (new numbering) and older versions, PortableOpenOffice 4.1.7 and older, StarOffice 5.2
---
Lupp from München
Post Reply