[Solved] Automatically highlighting the active row

Creating a macro - Writing a Script - Using the API

[Solved] Automatically highlighting the active row

Postby avworld » 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
Last edited by avworld on Fri Aug 26, 2011 10:20 pm, edited 1 time in total.
OpenOffice 3.3 on Windows 7
avworld
 
Posts: 7
Joined: Thu Aug 25, 2011 8:47 pm

Re: Automatically highlighting the active row

Postby Zizi64 » Thu Aug 25, 2011 9:21 pm

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   Expand viewCollapse view
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; LO4.4.7, LO5.3.7 on Win7x64Prof.
PortableApps, WinPenPack: LO3.3.0-LO5.4.3 and AOO4.1.4
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 6083
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Automatically highlighting the active row

Postby Villeroy » 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.
Attachments
crosshighlight.ods
Highlight row/col of active cell. Second try
(20.19 KiB) Downloaded 1105 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24664
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Automatically highlighting the active row

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

Re: Automatically highlighting the active row

Postby Villeroy » Thu Aug 25, 2011 10:29 pm

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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24664
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Automatically highlighting the active row

Postby avworld » Fri Aug 26, 2011 3:54 pm

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

Postby avworld » 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.
Last edited by avworld on Fri Aug 26, 2011 4:37 pm, edited 1 time in total.
OpenOffice 3.3 on Windows 7
avworld
 
Posts: 7
Joined: Thu Aug 25, 2011 8:47 pm

Re: Automatically highlighting the active row

Postby Zizi64 » 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 1034 times
Tibor Kovacs, Hungary; LO4.4.7, LO5.3.7 on Win7x64Prof.
PortableApps, WinPenPack: LO3.3.0-LO5.4.3 and AOO4.1.4
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 6083
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Automatically highlighting the active row

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

Re: Automatically highlighting the active row

Postby Zizi64 » 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...
Tibor Kovacs, Hungary; LO4.4.7, LO5.3.7 on Win7x64Prof.
PortableApps, WinPenPack: LO3.3.0-LO5.4.3 and AOO4.1.4
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 6083
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Automatically highlighting the active row

Postby Villeroy » 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   Expand viewCollapse view
   oRanges = oSheet.queryIntersection(oColAddr)
   oRanges.addRangeAddress(oRowAddr,false)
   g_Cross = oRanges
End Sub

Code: Select all   Expand viewCollapse view
   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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24664
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Automatically highlighting the active row

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

Re: Automatically highlighting the active row

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

Re: [Solved] Automatically highlighting the active row

Postby uruhiir » 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?
Openoffice 3.4 linux mint
uruhiir
 
Posts: 1
Joined: Fri Dec 23, 2011 4:45 pm

Re: [Solved] Automatically highlighting the active row

Postby Zizi64 » Fri Dec 23, 2011 9:40 pm

Tibor Kovacs, Hungary; LO4.4.7, LO5.3.7 on Win7x64Prof.
PortableApps, WinPenPack: LO3.3.0-LO5.4.3 and AOO4.1.4
Please, edit the topic's initial post, and add the word "[Solved]" at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 6083
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: [Solved] Automatically highlighting the active row

Postby Villeroy » 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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24664
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Automatically highlighting the active row

Postby rtochip » 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
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

Postby rtochip » 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.
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

Postby Villeroy » Mon Aug 19, 2013 7:53 pm

Well, select the row of the active cell then. The only modification is this:
Code: Select all   Expand viewCollapse view
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 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24664
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Automatically highlighting the active row

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

Re: Automatically highlighting the active row

Postby vipapi » 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   Expand viewCollapse view
   oRanges = oSheet.queryIntersection(oColAddr)
   oRanges.addRangeAddress(oRowAddr,false)
   g_Cross = oRanges
End Sub

Code: Select all   Expand viewCollapse view
   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
vipapi
 
Posts: 1
Joined: Mon Jan 09, 2017 12:48 pm

Re: [Solved] Automatically highlighting the active row

Postby Lupp » 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.
Attachments
focusRowColHighlight.ods
(26.68 KiB) Downloaded 28 times
On Windows 10: LibreOffice 5.4.2 and older versions, PortableOpenOffice 4.1.3 and older, StarOffice 5.2
---
Maybe we might! (Create a powerful UFO: United Free Office)
Lupp from München
User avatar
Lupp
Volunteer
 
Posts: 1523
Joined: Sat May 31, 2014 7:05 pm


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 7 guests