3

How can I highlight the current row and column like a crosshair in Libreoffice as guide for the eye?

crosshair in Libreoffice

There seems to be an open feature request from 2011 and a very similar question for openoffice - but nothing I can find on libreoffice and supported at the moment. How can I get LibreOffice to do this?

3

Lupp has recently created an example spreadsheet that uses macros for this behavior. It's posted at https://forum.openoffice.org/en/forum/viewtopic.php?t=43531#p431848.

However, he remarks at AskLO that:

It's a questionable approach to use "macros" for the purpose. The solution is only applicable to sheets that do not use hard (direct) cell formatting... Not actually recommended!

The only good solution would be if it could be implemented in LibreOffice. But as you may have read in the bug report:

Talking to one of the experienced developers...it's no trivial task. It might never be implemented.

As mentioned in my answer to that same question on AskLO, a practical solution is to use conditional formatting to add color to odd or even rows.

EDIT:

To reproduce Lupp's example starting from scratch, first go to Tools -> Macros -> Organize Macros -> LibreOffice Basic. Find the .ods document, press New to create a new module, and put the following code in the module.

Global focusCellAddressGl As String, columnWithFocusCellGl As Long, rowWithFocusCellGl As Long

Function focusCell(Optional pCtrl) As Object
REM Concept by "uros", "Villeroy"
REM Responsible for this variant: Wolfgang Jäger
REM 2017-09-28 V0
On Error Goto eExit
If IsMissing(pCtrl) Then pCtrl = ThisComponent.CurrentController
If  NOT pCtrl.SupportsService("com.sun.star.sheet.SpreadsheetView") Then Exit Function
    Dim theSheet As Object, fC As Object, sheetNum As Long, sInfo As String, sInfoDelim As String 
    Dim vD, vDSplit, sInfoSplit
vD             = pCtrl.ViewData
vDSplit        = Split(vD, ";")
theSheet       = pCtrl.ActiveSheet's(sheetNum)
sheetNum       = theSheet.RangeAddress.Sheet
sInfo          = vDSplit(sheetNum + 3)
REM For CellAddress.Row >= 8192 the "+" is used as the subdelimiter in ViewData. WHY?
If InStr(sInfo, "+")>0 Then 
    sInfoDelim = "+"
Else
    sInfoDelim = "/"
End If
sInfoSplit     = Split(sInfo, sInfoDelim)
fC             = theSheet.GetCellByPosition(sInfoSplit(0), sInfoSplit(1))
focusCell      = fC
eExit:
End Function 

Function focusCellAddress(Optional pDummy)
On Error Goto eExit
If focusCellAddressGl="" Then onSelectionChanged(NULL)
focusCellAddress=focusCellAddressGl
eExit:
End Function

Function columnWithFocusCell(Optional pDummy)
On Error Goto eExit
If columnWithFocuscellGl=0 Then onSelectionChanged(NULL)
columnWithFocusCell=columnWithFocusCellGl
eExit:
End Function

Function rowWithFocusCell(Optional pDummy)
On Error Goto eExit
If rowWithFocuscellGl=0 Then onSelectionChanged(NULL)
rowWithFocusCell=rowWithFocusCellGl
eExit:
End Function

Sub onSelectionChanged(pEvent)
On Error Goto eExit
tFC=focusCell()
focusCellAddressGl=Split(tFC.AbsoluteName,".")(1)
With tFC.CellAddress
columnWithFocusCellGl=.Column+1
rowWithfocusCellGl=.Row+1
End With
specCell=tFC.Spreadsheet.GetCellByPosition(0,0)
specCell.String = tFC.AbsoluteName
eExit:
End Sub 

Now, right-click on the tab of the current sheet and choose Sheet Events. Assign onSelectionChanged to the "Selection changed" event.

Also, create style cfFocusCross with a background color.

Finally, go to Format -> Conditional Formatting -> Manage -> Add.

  • Formula is OR(ROW(A1)=ROWWITHFOCUSCELL();COLUMN(A1)=COLUMNWITHFOCUSCELL())+N($A$1)*0
  • Apply style cfFocusCross
  • Range A1:Z100

spreadsheet with row and column highlighted

  • I suggest to add the complete code here, because users will probably still visit the sx page when the forum already moved, switched the software or the URL is dead. – Jonas Stein Dec 20 '17 at 9:37
  • @Jonas Stein: OK, code has been added along with instructions. – Jim K Dec 20 '17 at 17:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.