Apache OpenOffice Community Forum

Posted: **Thu Aug 25, 2011 9:05 pm**

I'm pretty sure I know the answer to this question as I've been searching around a bit already, but is there a straightforward way to "highlight" the row of the active cell automatically (without having to keep hitting shift-space)?
The "highlight" bar would always be on the row of the active cell(s), ideally highlighting in a subtle color leaving the text behind very readable (very pale yellow for example).
If there are a large number of columns (on a widescreen monitor) it gets difficult to look back down the row to verify data.
Is there a quick way to do this using conditional formatting?
It would actually be a pretty useful feature to add to OO, even as a plugin you could activate/deactivate.
Thanks,
Steve

Posted: **Thu Aug 25, 2011 9:21 pm**

Hi,

If I understood your problen exactly:
Just click on number of row...:

Or you can assign next macro to a "moving" event (I do not now how to do that):

Code: Select all

sub Highlight_row
rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:SelectRow", "", 0, Array())
end sub

Posted: **Thu Aug 25, 2011 9:52 pm**

Put the attached spreadsheet in a trusted directory according to Tools>Options>Security>Macro Security and give a try.
I moved this topic to the macro forum.

Posted: **Thu Aug 25, 2011 10:10 pm**

Thanks Villeroy,
Unfortunately when I open the file the rows/columns for D15 are highlighted, but they stay highlighted when I move to another cell, and the new active cell isn't highlighted.
Am I missing something? I set that directory as a trusted location, and I even set the macro security to low, but no change.

Posted: **Thu Aug 25, 2011 10:29 pm**

Oh, sorry. I made a mistake. I replaced the document with another one one.

Posted: **Fri Aug 26, 2011 3:54 pm**

Thanks for the help Villeroy. I take it there's a lot going on there.

Posted: **Fri Aug 26, 2011 4:17 pm**

Only one problem with that solution - you can't use highlighting on any cells, and it wipes out any cell bgcolor that was there previously.
I can't imagine there's an easy fix for that either, but my knowledge of basic is pretty limited.
If maintaining cell bgcolor and allowing one to still set bgcolors for cells isn't possible, maybe using a less-used cell format like a shadow at 1pt distance would interfere less with functionality.
Would it be straightforward to change the format?
I'm becoming a pain in the ass.

Posted: **Fri Aug 26, 2011 4:35 pm**

If it is enough to highlight a ROW only, then you can combine my recorded macro with Villeroy's event handler:

crosshighlight_combined.ods: (21.12 KiB) Downloaded 2020 times

Posted: **Fri Aug 26, 2011 4:49 pm**

That's exactly what I'm looking for.
Unfortunately I'm getting errors using the turn-off button - see attached.

** just realized it only happens when the off button is pressed twice, so it's not really a big deal.
Is it possible to float the buttons, or would the best way to keep them visible be to freeze the row below them?
Thanks!

Posted: **Fri Aug 26, 2011 5:04 pm**

** just realized it only happens when the off button is pressed twice, so it's not really a big deal.
Is it possible to float the buttons, or would the best way to keep them visible be to freeze the row below them?
Thanks!

Really... There is not handler for this case... Sorry.
You need to realize error handling, need examine all of the conditions in this macro "skeleton".

If you need use this feature in all of your ods files, you must to copy this macro from the ods document to the MyMacros Library of OpenOffice, and you can assign the ON/OFF subroutine to two user defined menu items, or to toolbox icons, or to hotkeys...

Posted: **Fri Aug 26, 2011 5:47 pm**

Only one problem with that solution - you can't use highlighting on any cells, and it wipes out any cell bgcolor that was there previously.
I can't imagine there's an easy fix for that either, but my knowledge of basic is pretty limited.

You just need to know the application. Simply use cell styles. The code applies and removes hard formatting without touching the styles.

Highlighting a row is easy. Change the last lines of Module2

Code: Select all

   oRanges = oSheet.queryIntersection(oColAddr)
   oRanges.addRangeAddress(oRowAddr,false)
   g_Cross = oRanges
End Sub

Code: Select all

   oRanges = oSheet.queryIntersection(oRowAddr)
REM   oRanges.addRangeAddress(oRowAddr,false)
   g_Cross = oRanges
End Sub

Posted: **Mon Aug 29, 2011 8:37 pm**

Zizi64 wrote: ...If you need use this feature in all of your ods files, you must to copy this macro from the ods document to the MyMacros Library of OpenOffice, and you can assign the ON/OFF subroutine to two user defined menu items, or to toolbox icons, or to hotkeys...

This worked perfectly - Now I have an on and off toolbar button for this function.
Thanks for the help!

Posted: **Mon Aug 29, 2011 8:40 pm**

Villeroy wrote: ...You just need to know the application. Simply use cell styles. The code applies and removes hard formatting without touching the styles...

Thanks Villeroy - I'll have to delve further into OoBasic at some point.

Posted: **Fri Dec 23, 2011 4:49 pm**

Great work. I was just looking for the same thing, and I also think it should be added to program. But I have small problem concerning how to save it to Mymacros. I have tried quite some time, but no luck so far. Would anyone explain it please?

Posted: **Fri Dec 23, 2011 9:40 pm**

[Tutorial] How to install a code snippet

Posted: **Fri Dec 23, 2011 11:33 pm**

Tools>Macros>Organize>[Organizer...]>[Libraries],section MyMacros, [Import...] Point to the downloaded file and import the Highlight library.

Posted: **Mon Aug 19, 2013 10:54 am**

Hello, I tried the codes and it works to highlight the column/row of the active selected cell. Thanks.

However, this only works for read/write files since it alters the background color of the cells. How do I use this for read-only file? Perhaps put in a macro similarly what the function of "shift-space" does?

Thanks
Ramon

Posted: **Mon Aug 19, 2013 12:35 pm**

This works on read/write file. It does not work on read-only file. Is there a way to make this work on read-only file? It does not work on read-only file because it changes the background colour of the selected cells in the file.

Can anybody help? It is used by several people to search items and we don't want them to accidentally edit data on the spreadsheet while searching.

Posted: **Mon Aug 19, 2013 7:53 pm**

Well, select the row of the active cell then. The only modification is this:

Code: Select all

Sub FormatCrossSelection()
   setCrossSelection
   ThisComponent.CurrentController.select(g_Cross)
End Sub

Edit: which makes your sheet unusable because it selects the row or the cross around the active cell while activating the cell in column A. I don't know of any way how to activate a particular cell within a selection nor how to select some range without changing the active cell.

Posted: **Wed Aug 21, 2013 10:51 am**

Thanks.

I also used

dispatcher.executeDispatch(document, ".uno:SelectRow", "", 0, Array())

from zizi64 above

and seems to work.

Posted: **Mon Jan 09, 2017 1:02 pm**

Villeroy wrote:
Only one problem with that solution - you can't use highlighting on any cells, and it wipes out any cell bgcolor that was there previously.
I can't imagine there's an easy fix for that either, but my knowledge of basic is pretty limited.
You just need to know the application. Simply use cell styles. The code applies and removes hard formatting without touching the styles.

Highlighting a row is easy. Change the last lines of Module2
Code: Select all
   oRanges = oSheet.queryIntersection(oColAddr)
   oRanges.addRangeAddress(oRowAddr,false)
   g_Cross = oRanges
End Sub
Code: Select all
   oRanges = oSheet.queryIntersection(oRowAddr)
REM   oRanges.addRangeAddress(oRowAddr,false)
   g_Cross = oRanges
End Sub

Hello Villeroy, I have used your code for my spreadsheet, I have many cells with "background color" and their code lines remove the "background color"
Is it possible to change lines of code to maintain the "background color"?
Sorry for my English
regards

Posted: **Tue Nov 14, 2017 3:02 am**

The thread is more than six years old and the appended question above will also soon have birthday.
I would suppose some of the contributors will not actually suggest to use the posted solutions.
The issue of direct formatting being overwritten was already discussed: Use named cell styles.

Coming from https://ask.libreoffice.org/en/question ... tive-cell/
I will nonetheless offer a partly different solution based again, of course, on intercepting the 'Change Selection' event (now not by an explicitly registered listener but via the SheetEvent) but then using a conditional format to overlay the highlight color without overwriting the formerly set CellBackcolor.
Lots of disadvantages. Just for completeness. Not recommended.

Apache OpenOffice Community Forum

[Solved] Automatically highlighting the active row

[Solved] Automatically highlighting the active row

Re: Automatically highlighting the active row

Re: Automatically highlighting the active row

Re: Automatically highlighting the active row

Re: Automatically highlighting the active row

Re: Automatically highlighting the active row

Re: Automatically highlighting the active row

Re: Automatically highlighting the active row

Re: Automatically highlighting the active row

Re: Automatically highlighting the active row

Re: Automatically highlighting the active row

Re: Automatically highlighting the active row

Re: Automatically highlighting the active row

Re: [Solved] Automatically highlighting the active row

Re: [Solved] Automatically highlighting the active row

Re: [Solved] Automatically highlighting the active row

Re: [Solved] Automatically highlighting the active row

Re: [Solved] Automatically highlighting the active row

Re: [Solved] Automatically highlighting the active row

Re: [Solved] Automatically highlighting the active row

Re: Automatically highlighting the active row

Re: [Solved] Automatically highlighting the active row