Highlighting row when choosing a cell

Chip · October 23, 2023, 9:14am

Hi there,
first of all: Great to be here & posting my first thread.

I’d like to highlight a whole row when choosing a cell.
I did some research & found a macro which actually works great:

EM  *****  BASIC  *****

global noldrow as integer
global osheet as object
global bfirst as boolean

Sub S_change_cellbackcolor_in_actual_row(event)
 
    if not bfirst then goto 100
    S_remove_cellbackcolor_in_old_row
    100:
    ocell = event
    if ocell.supportsservice("com.sun.star.sheet.SheetCell") then
       ocelladdress = ocell.celladdress
       nsheet = ocelladdress.sheet
       osheet = thiscomponent.sheets(nsheet)
       nRow = ocelladdress.Row
       noldrow = nRow
       orange = osheet.getcellrangebyposition(0,nRow,27,nRow)'A=1 bis AB=27
       if orange.CellBackColor <> RGB(180,199,220) then ThisComponent.getDocumentProperties().UserDefinedProperties.oldfarbe = orange.CellBackColor
       orange.CellBackColor = RGB(180,199,220)
       orange.CharWeight = com.sun.star.awt.FontWeight.BOLD
    endif
    bfirst = true
End Sub

Sub S_remove_cellbackcolor_in_old_row
    orange = osheet.getcellrangebyposition(0,noldRow,27,noldRow)'A=1 bis AB=27
    orange.CellBackColor = ThisComponent.getDocumentProperties().UserDefinedProperties.oldfarbe
    orange.CharWeight = com.sun.star.awt.FontWeight.NORMAL
End Sub

Sub Errortest
  On Error GoTo Handler
i = 1/(1)
  Exit Sub
  Handler:
  msgbox "Error: " & Error
End Sub

The problem is the following:
I’m using already some background color in the rows, but executing the macro all my previous background colors are gone, i. e. deleted. That’s why, for example, a previous red row, in the end appears only white.

How can I fix this?
How do I have to change the above macro?

Thanks in advance for your advices & support!
Chipy

Lupp · October 23, 2023, 9:31am

Visit tdf#33201 to see that others also don’t know a satisfying solution.
The restitution of the previous appearance ist the relevant problem with any workaround. The workaround you found isn’t the only one, but afaik ,there is none which handles the problem in a stable way.
Core development needed.

Chip · October 24, 2023, 10:40am

Hey Lupp,

thank you for your reply.
What a pitty!

The workarounds you talked about - could you provide me with some links, please?
I couldn’t find anythin else, when I searched for it.

Thanks in advance!
Chipy

JohnSUN · October 24, 2023, 1:04pm

This was written and published on 02/02/2012. In fairness, it should be said that it didn’t work very well then, and it doesn’t work well enough now - Highlight row and column with active cell.ods (22.7 KB)

The idea was to add another conditional format to the current “cross” (column + row) from the active cell, which will actually draw bright borders, and when the active cell changes, it will delete this conditional formatting and create it in a new place. The fact is that it is the use of conditional formatting that allows you to preserve the original formatting of the cells without spoiling it. However, the problem is that such massive conditional formatting leaves behind artifacts and does not erase red lines well. Therefore, I had to add a third button to the user panel to clear these lines (sometimes you have to press this button twice or three times). I’m not suggesting this as a solution, perhaps you can use the idea to create something more reliable.
I did not translate the comments and inscriptions on the buttons, this is exactly how it was created in 2012 - then I still did not disdain communicating with people in this language

Lupp · October 24, 2023, 1:14pm

I remember in general that there were questions and answers (partly suggesting workarounds) on the subject in several places over the years. The problem of restoring the “previous appearance” after changing the focus was never solved. Therefore, these workarounds are not suitable for sheets where hard attributes (the properties, not the values) are used for highlighting which are also used for different purposes.
Because I considered all that stuff rather low-value, I never kept track of such threads.
One example I could easily find now is Apache OpenOffice Community Forum - Highlight cells determined by active cell - (View topic).

Chip · October 24, 2023, 4:09pm

Thanks for your advices.
The example you provided I had found it also.
I see and understand how it is implemented within the attached file (Tools → Customize → [Events]).
But by any chance I can’t get it to work on my own file.

What am I doing wrong?

Chip · October 24, 2023, 4:10pm

Thank you very much for your advice, too.

I see and understand how it is implemented within the attached file (Tools → Customize → [Events]).
But by any chance I can’t get it to work on my own file.

What am I doing wrong?