[Solved] Automatically highlighting the active row

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
avworld
Posts: 7
Joined: Thu Aug 25, 2011 8:47 pm

[Solved] Automatically highlighting the active row

Post by avworld »

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
Last edited by avworld on Fri Aug 26, 2011 10:20 pm, edited 1 time in total.
OpenOffice 3.3 on Windows 7
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Automatically highlighting the active row

Post by Zizi64 »

Hi,

If I understood your problen exactly:
Just click on number of row...:
Highlight_row.png
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
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Automatically highlighting the active row

Post by Villeroy »

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.
Attachments
crosshighlight.ods
Highlight row/col of active cell. Second try
(20.19 KiB) Downloaded 2240 times
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
avworld
Posts: 7
Joined: Thu Aug 25, 2011 8:47 pm

Re: Automatically highlighting the active row

Post by avworld »

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.
OpenOffice 3.3 on Windows 7
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Automatically highlighting the active row

Post by Villeroy »

Oh, sorry. I made a mistake. I replaced the document with another one one.
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
avworld
Posts: 7
Joined: Thu Aug 25, 2011 8:47 pm

Re: Automatically highlighting the active row

Post by avworld »

Thanks for the help Villeroy. I take it there's a lot going on there.
OpenOffice 3.3 on Windows 7
avworld
Posts: 7
Joined: Thu Aug 25, 2011 8:47 pm

Re: Automatically highlighting the active row

Post by avworld »

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.
Last edited by avworld on Fri Aug 26, 2011 4:37 pm, edited 1 time in total.
OpenOffice 3.3 on Windows 7
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Automatically highlighting the active row

Post by Zizi64 »

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 1795 times
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
avworld
Posts: 7
Joined: Thu Aug 25, 2011 8:47 pm

Re: Automatically highlighting the active row

Post by avworld »

That's exactly what I'm looking for.
Unfortunately I'm getting errors using the turn-off button - see attached.
OOBasicError_RowHighlight.jpg
** 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!
OpenOffice 3.3 on Windows 7
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Automatically highlighting the active row

Post by Zizi64 »

** 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...
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Automatically highlighting the active row

Post by Villeroy »

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
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
avworld
Posts: 7
Joined: Thu Aug 25, 2011 8:47 pm

Re: Automatically highlighting the active row

Post by avworld »

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!
OpenOffice 3.3 on Windows 7
avworld
Posts: 7
Joined: Thu Aug 25, 2011 8:47 pm

Re: Automatically highlighting the active row

Post by avworld »

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.
OpenOffice 3.3 on Windows 7
uruhiir
Posts: 1
Joined: Fri Dec 23, 2011 4:45 pm

Re: [Solved] Automatically highlighting the active row

Post by uruhiir »

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?
Openoffice 3.4 linux mint
User avatar
Zizi64
Volunteer
Posts: 11353
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Automatically highlighting the active row

Post by Zizi64 »

Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Automatically highlighting the active row

Post by Villeroy »

Tools>Macros>Organize>[Organizer...]>[Libraries],section MyMacros, [Import...] Point to the downloaded file and import the Highlight library.
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
rtochip
Posts: 8
Joined: Mon Aug 19, 2013 10:49 am

Re: [Solved] Automatically highlighting the active row

Post by rtochip »

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
OpenOffice 3.1 on Ubuntu 12.4
rtochip
Posts: 8
Joined: Mon Aug 19, 2013 10:49 am

Re: [Solved] Automatically highlighting the active row

Post by rtochip »

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.
OpenOffice 3.1 on Ubuntu 12.4
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Automatically highlighting the active row

Post by Villeroy »

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. 
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
rtochip
Posts: 8
Joined: Mon Aug 19, 2013 10:49 am

Re: [Solved] Automatically highlighting the active row

Post by rtochip »

Thanks.

I also used

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

from zizi64 above

and seems to work.
OpenOffice 3.1 on Ubuntu 12.4
vipapi
Posts: 1
Joined: Mon Jan 09, 2017 12:48 pm

Re: Automatically highlighting the active row

Post by vipapi »

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
OpenOffice 4.1 on Mint 18.1
User avatar
Lupp
Volunteer
Posts: 3542
Joined: Sat May 31, 2014 7:05 pm
Location: München, Germany

Re: [Solved] Automatically highlighting the active row

Post by Lupp »

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.
Attachments
focusRowColHighlight.ods
(26.68 KiB) Downloaded 1075 times
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