Excel - Highlighting (underlining) the active row

Post your working scripts, libraries and tools
User avatar
haichen
Posts: 592
Joined: 09 Feb 2014, 08:24

Excel - Highlighting (underlining) the active row

17 Dec 2020, 02:19

As the title says, it puts a line under the active row.
Why not use an Excel macro? Because all VBA macros in Excel clear the UnDo stack.

You can select the color and thickness of the line (right click systrayicon), and pause the program by double clicking on the systrayicon.

Code: Select all

#Persistent
#SingleInstance


Tiptext =
(
Draws a line under the active Excel selection
in the menu color, line thickness can be selected
Double click = Pause
)


Hilfe =
(
Draws a line under the active Excel selection
in the menu color, line thickness and can be selected
)



Menu, Tray, NoStandard
Menu, tray,add,  ExcelHelpLine
Menu, tray,Disable,ExcelHelpLine

Menu, tray, add
Menu, tray,add, red

Menu, tray,add, green
Menu, tray,add, yellow
Menu, tray,add, blue
Menu, tray,add, black

Menu, tray, add
Menu, tray,add, H1
Menu, tray,add, H2

Menu, tray,add, H3
Menu, tray,add, H5
Menu, tray, add
Menu, Tray, Tip, %Tiptext%
Menu, tray, add, help
Menu, tray, add
Menu, tray,add, Pause
Menu, Tray, Default, Pause
Menu, tray, add, exit
Menu, tray, NoStandard

;=================
; Preset
color:="cc0000"
Menu, Tray, Disable,  red
line:=2
Menu, Tray, Disable,  H2
;=================

SetTimer, rule ,100
return

rule:
ruler(color,line)
return

ruler(color,line){
static yold:=0, wold:=0, hold:=0, xold:=0, run:=0

if (r and !WinActive("ahk_class XLMAIN"))
{
	SplashImage, off
	run:=0
}
	

If !IsObject(xl)
{
try
		xl := ComObjActive("Excel.Application")
	catch
	{
		SplashImage, off
		wold:=-1
		return
	}
}

if hwnd:=WinActive("ahk_class XLMAIN")
{
	run:=1
	
	try
	{
		c:=xl.selection.rows.count  ; counts the lines of the selection
		y1:=xl.ActiveWindow.ActivePane.PointsToScreenPixelsY(xl.selection.offset(c,0).top) ; screen position of the selection shifted by its number of lines
		WinGetPos, x, Cy, Width, Height, ahk_id %hwnd%
		ControlGetFocus, WhichControl, ahk_id %hwnd%
		;MouseGetPos, , , WhichWindow, WhichControl ; Alternativly
		
		if (WhichControl="NetUIHWND1")
		{
			SplashImage, off
			wold:=-1
			return
		}
			
		if (yold <> y1) or (xold <> x) or (hold <> height) or (wold <> width)
		{
			r:="a" round(xl.ActiveWindow.ActivePane.ScrollRow //1,0)
			yr:=xl.ActiveWindow.ActivePane.PointsToScreenPixelsY(xl.range(r).top)-10	
											
			if (y1<yr) or (y1>(cy+height-20))
			{
				SplashImage, off
				return
			}
			
			Splashimage,, B W%Width% H%line% Y%y1% X%x% CW%color% ; create line only on change
			yold:=y1
			wold:=width
			hold:=height
			xold:= x
		
		}
	}
}

else
{
	SplashImage, off
	yold:=0, wold:=0, hold:=0, xold:=0, run:=0
}
return
}


ExitApp
return

red:
color:="cc0000"
gosub Menuchange1
Menu, Tray, Disable, red
WinActivate, ahk_class XLMAIN
return

green:
color:="00cc00"
gosub Menuchange1
Menu, Tray, Disable, green
WinActivate, ahk_class XLMAIN
return

yellow:
color:="efef00"
gosub Menuchange1
Menu, Tray, Disable, yellow
WinActivate, ahk_class XLMAIN
return

blue:
color:="00cccc"
gosub Menuchange1
Menu, Tray, Disable, blue
WinActivate, ahk_class XLMAIN
return

black:
color:="000000"
gosub Menuchange1
Menu, Tray, Disable, black
WinActivate, ahk_class XLMAIN
return 


h1:
line:=1
gosub Menuchange2
Menu, Tray, Disable, H1
WinActivate, ahk_class XLMAIN
return

h2:
line:=2
gosub Menuchange2
Menu, Tray, Disable, H2
WinActivate, ahk_class XLMAIN
return

h3:
line:=3
gosub Menuchange2
Menu, Tray, Disable, H3
WinActivate, ahk_class XLMAIN
return

h5:
line:=5
gosub Menuchange2
Menu, Tray, Disable, H5
WinActivate, ahk_class XLMAIN
return


Menuchange1:
Gui, Submit , NoHide
Menu, Tray, Enable, red
Menu, Tray, Enable, green
Menu, Tray, Enable, yellow
Menu, Tray, Enable, blue
Menu, Tray, Enable, black
return

Menuchange2:
Gui, Submit , NoHide
Menu, Tray, Enable, H1
Menu, Tray, Enable, H2
Menu, Tray, Enable, H3
Menu, Tray, Enable, H5

return

help:
Msgbox, %Hilfe%
return

exit:
exitapp

ExcelHelpLine:
return


Pause:
Pause 
WinActivate, ahk_class XLMAIN
return
Edit:
Looks like this:
2020-12-11_10h36_19.png
2020-12-11_10h36_19.png (93.34 KiB) Viewed 755 times
Edit 24.01.2021: Improvement by daywalker
Last edited by haichen on 24 Jan 2021, 05:09, edited 1 time in total.
burque505
Posts: 1447
Joined: 22 Jan 2017, 19:37

Re: Excel - Highlighting (underlining) the active row

17 Dec 2020, 08:24

@haichen, thanks very much for this. Working great, although I had to use Excel_Get.ahk instead of ComObjActive with my Office 16 setup on Win7 64-bit.

Code: Select all

#Persistent
#SingleInstance
#Include Excel_Get.ahk

Code: Select all

If !IsObject(xl)
{
	try
		;xl := ComObjActive("Excel.Application")
		xl := Excel_Get()
	catch
		return
}
Here is a copy of Excel_Get.ahk for anyone who needs it and can't find it on the forum.
Spoiler
Regards, and thanks!
burque505
User avatar
haichen
Posts: 592
Joined: 09 Feb 2014, 08:24

Re: Excel - Highlighting (underlining) the active row

17 Dec 2020, 08:57

Hello burque505,
I did not know that ComObjActive does not work with office 2016.
I also do not have office 16 to test.
Thank you very much, that you have brought the solution,
I would have had to search for a long time.

I hope this helps others who are also having problems getting the script to run.

thanks a lot
haichen

My system
Office 365
Windows 10 Pro
20H2 10.0.19042.685
AutoHotkey: 1.1.33.02
burque505
Posts: 1447
Joined: 22 Jan 2017, 19:37

Re: Excel - Highlighting (underlining) the active row

17 Dec 2020, 09:01

Hi @haichen, as far as Office 16 goes, I think ComObjActive works for some, but not for others, thus my post.
Again, thanks!
Regards,
burque505
User avatar
DataLife
Posts: 371
Joined: 29 Sep 2013, 19:52

Re: Excel - Highlighting (underlining) the active row

17 Dec 2020, 18:23

Worked great on first try.

I just copy and pasted your script and it worked on Microsoft Excel for Microsoft 365.

thanks
Check out my scripts. (MyIpChanger) (ClipBoard Manager) (SavePictureAs)
All my scripts are tested on Windows 10, AutoHotkey 32 bit Ansi unless otherwise stated.
User avatar
hasantr
Posts: 780
Joined: 05 Apr 2016, 14:18
Location: İstanbul

Re: Excel - Highlighting (underlining) the active row

19 Dec 2020, 03:23

haichen wrote:
17 Dec 2020, 02:19
As the title says, it puts a line under the active row.
Why not use an Excel macro? Because all VBA macros in Excel clear the UnDo stack.

You can select the color and thickness of the line (right click systrayicon), and pause the program by double clicking on the systrayicon.

Code: Select all

#Persistent
#SingleInstance


Tiptext =
(
Draws a line under the active Excel selection
in the menu color, line thickness can be selected
Double click = Pause
)


Hilfe =
(
Draws a line under the active Excel selection
in the menu color, line thickness and can be selected
)



Menu, Tray, NoStandard
Menu, tray,add,  ExcelHelpLine
Menu, tray,Disable,ExcelHelpLine

Menu, tray, add
Menu, tray,add, red

Menu, tray,add, green
Menu, tray,add, yellow
Menu, tray,add, blue
Menu, tray,add, black

Menu, tray, add
Menu, tray,add, H1
Menu, tray,add, H2

Menu, tray,add, H3
Menu, tray,add, H5
Menu, tray, add
Menu, Tray, Tip, %Tiptext%
Menu, tray, add, help
Menu, tray, add
Menu, tray,add, Pause
Menu, Tray, Default, Pause
Menu, tray, add, exit
Menu, tray, NoStandard

;=================
; Preset
color:="cc0000"
Menu, Tray, Disable,  red
line:=2
Menu, Tray, Disable,  H2
;=================

SetTimer, rule ,100
return

rule:
ruler(color,line)
return

ruler(color,line){
static yold:=0, wold:=0, hold:=0, xold:=0, run:=0

if (r and !WinActive("ahk_class XLMAIN"))
{
	SplashImage, off
	run:=0
}
	

If !IsObject(xl)
{
	try
		xl := ComObjActive("Excel.Application")
	catch
		return
}

if WinActive("ahk_class XLMAIN")
{
	run:=1
	
	try
	{
		c:=xl.selection.rows.count  ; counts the lines of the selection
		y1:=xl.ActiveWindow.ActivePane.PointsToScreenPixelsY(xl.selection.offset(c,0).top) ; screen position of the selection shifted by its number of lines
		WinGetPos, x, Cy, Width, Height, a
		
		if (yold <> y1) or (xold <> x) or (hold <> height) or (wold <> width)
		{
			r:="a" round(xl.ActiveWindow.ActivePane.ScrollRow //1,0)
			yr:=xl.ActiveWindow.ActivePane.PointsToScreenPixelsY(xl.range(r).top)-10	
											
			if (y1<yr) or (y1>(cy+height-20))
			{
				SplashImage, off
				return
			}
			
			Splashimage,, B W%Width% H%line% Y%y1% X%x% CW%color% ; create line only on change
			yold:=y1
			wold:=width
			hold:=height
			xold:= x
		
		}
	}
}
else
{
	SplashImage, off
	yold:=0, wold:=0, hold:=0, xold:=0, run:=0
}
return
}




ExitApp
return

red:
color:="cc0000"
gosub Menuchange1
Menu, Tray, Disable, red
WinActivate, ahk_class XLMAIN
return

green:
color:="00cc00"
gosub Menuchange1
Menu, Tray, Disable, green
WinActivate, ahk_class XLMAIN
return

yellow:
color:="efef00"
gosub Menuchange1
Menu, Tray, Disable, yellow
WinActivate, ahk_class XLMAIN
return

blue:
color:="00cccc"
gosub Menuchange1
Menu, Tray, Disable, blue
WinActivate, ahk_class XLMAIN
return

black:
color:="000000"
gosub Menuchange1
Menu, Tray, Disable, black
WinActivate, ahk_class XLMAIN
return 


h1:
line:=1
gosub Menuchange2
Menu, Tray, Disable, H1
WinActivate, ahk_class XLMAIN
return

h2:
line:=2
gosub Menuchange2
Menu, Tray, Disable, H2
WinActivate, ahk_class XLMAIN
return

h3:
line:=3
gosub Menuchange2
Menu, Tray, Disable, H3
WinActivate, ahk_class XLMAIN
return

h5:
line:=5
gosub Menuchange2
Menu, Tray, Disable, H5
WinActivate, ahk_class XLMAIN
return


Menuchange1:
Gui, Submit , NoHide
Menu, Tray, Enable, red
Menu, Tray, Enable, green
Menu, Tray, Enable, yellow
Menu, Tray, Enable, blue
Menu, Tray, Enable, black
return

Menuchange2:
Gui, Submit , NoHide
Menu, Tray, Enable, H1
Menu, Tray, Enable, H2
Menu, Tray, Enable, H3
Menu, Tray, Enable, H5

return

help:
Msgbox, %Hilfe%
return

exit:
exitapp

ExcelHelpLine:
return


Pause:
Pause 
WinActivate, ahk_class XLMAIN
return
Edit:
Looks like this:2020-12-11_10h36_19.png
This is great. I liked highlighting the selected line. But I couldn't use it with vba because it blocked the Undo feature. Good idea.
User avatar
haichen
Posts: 592
Joined: 09 Feb 2014, 08:24

Re: Excel - Highlighting (underlining) the active row

21 Dec 2020, 03:49

I am very happy that there is interest for this script!!!
Glad you like it.

I wrote this originally as a VBA macro.
But because users couldn't use undo anymore I developed it as an autohotkeyscript.
For Excel there is also a Javascript-api to write add-ins.
So I tried to make changes to Excel with it.
The result is the same. The UnDo stack is deleted with every change.

Users who want to use CTRL+Z (STRG+Z) can't make changes in Excel with VBA, Javascript and also via AutoHotkey COM.
Here I can not understand Microsoft at all.
User avatar
hasantr
Posts: 780
Joined: 05 Apr 2016, 14:18
Location: İstanbul

Re: Excel - Highlighting (underlining) the active row

21 Dec 2020, 04:33

Can slight delay be corrected? Using ~Lbutton instead of a timer. I tried little and gave up quickly because I didn't have time
User avatar
haichen
Posts: 592
Joined: 09 Feb 2014, 08:24

Re: Excel - Highlighting (underlining) the active row

21 Dec 2020, 05:10

It does not work without settimer.
If the window was static, it would work.
But you probably also want to be able to scroll.
And move the window, zoom in or out.
Each time the position and length must be updated.
Therefore your wish probably doesn't work.

You might be able to reduce the delay by changing the timer time.
samzeile
Posts: 1
Joined: 12 Jul 2020, 23:30

Re: Excel - Highlighting (underlining) the active row

21 Dec 2020, 22:02

It works just great on first try.
User avatar
kczx3
Posts: 1254
Joined: 06 Oct 2015, 21:39

Re: Excel - Highlighting (underlining) the active row

22 Dec 2020, 10:36

It would be a lot more work but I'd probably go with using an actual Excel event to handle this. Here's a rough start:

Code: Select all

#Persistent

global color := "00cccc", line := 2

xl := ComObjCreate("Excel.Application")
xl.visible := true

ComObjConnect(xl, "xl_")

; 0x3 - EVENT_SYSTEM_FOREGROUND
DllCall("SetWinEventHook", "UInt", 0x3, "UInt", 0x3, "Ptr", 0, "Ptr", RegisterCallback("OnWindowChange"), "UInt", 0, "UInt", 0, "UInt", 0)

OnExit((*) => (xl.quit(), xl := ""))

xl_SheetSelectionChange(sheet, range, xl) {
    static yold:=0, wold:=0, hold:=0, xold:=0, run:=0
    
    ; There may be a better event to use as the passed range seems to just be the whole worksheet
    c := sheet.application.selection.rows.rows.count  ; counts the lines of the selection
    y1 := sheet.application.ActiveWindow.ActivePane.PointsToScreenPixelsY(sheet.application.selection.offset(c, 0).top) ; screen position of the selection shifted by its number of lines
    WinGetPos, x, Cy, Width, Height, a
    
    if (yold <> y1 || xold != x || hold != height || wold != width) {
        r := "a" . round(sheet.application.ActiveWindow.ActivePane.ScrollRow // 1, 0)
        yr := sheet.application.ActiveWindow.ActivePane.PointsToScreenPixelsY(sheet.application.range(r).top)-10	
        
        Splashimage,, B W%Width% H%line% Y%y1% X%x% CW%color% ; create line only on change
        yold := y1
        wold := width
        hold := height
        xold := x
    
    }
}

/**
 * Function that monitors when the active window changes and starts/stops the input hook so that it only functions
 * on our window
*/
OnWindowChange(hWinEventHook, vEvent, hwnd) {
    global xl
    WinGetClass, class, % "ahk_id " . hwnd
    
    ToolTip, % class
    
    if (class == "XLMAIN") {
        ComObjConnect(xl, "xl_")
    }
    else {
        ComObjConnect(xl)
        SplashImage, off
    }
}
User avatar
haichen
Posts: 592
Joined: 09 Feb 2014, 08:24

Re: Excel - Highlighting (underlining) the active row

22 Dec 2020, 12:32

Seems to be to much advanced for me.
I don't understand these callbacks and dll calls well enough..
And to rough.

But I get these errors:
comobject error and
"The maximum number of MsgBoxes has been reached."

And there are a lot of things that do not work:
Does not work with multiple Excel windows.
Switching windows.
Bar does not follow scrolling.
Move windows, zoom in, zoom out.
Open and close windows.

I would like to try to understand what the script is doing there.
Thanks for your suggestion.
User avatar
kczx3
Posts: 1254
Joined: 06 Oct 2015, 21:39

Re: Excel - Highlighting (underlining) the active row

22 Dec 2020, 13:44

It makes use of events provided by the Excel COM implementation instead of relying on a timer that runs every 100 milliseconds.

Also, no idea why you'd get that from my code as what I provided doesn't include any MsgBoxes.
User avatar
haichen
Posts: 592
Joined: 09 Feb 2014, 08:24

Re: Excel - Highlighting (underlining) the active row

22 Dec 2020, 14:10

Yes, I also find it strange. Let's see if I find out something after :xmas:
SeeYouAtTop
Posts: 4
Joined: 22 Dec 2020, 13:27

Re: Excel - Highlighting (underlining) the active row

23 Dec 2020, 02:43

@haichen
@haichen
Great Script Indeed, Please tell What part of script I need to Change in order highlight all borders of active cell as well. I tried but have't succeed.
User avatar
haichen
Posts: 592
Joined: 09 Feb 2014, 08:24

Re: Excel - Highlighting (underlining) the active row

23 Dec 2020, 06:38

This is not easily doable.
I have written the script for the purpose of preserving the Excel own UnDo.
Therefore I can't take Excel commands, because every change deletes the UnDo stack. The only way is to read the size of the active cell and create a frame with Autohotkey.
And besides, yes, there is already a frame around the active cell....
Maybe you can create it with this infomation.
SeeYouAtTop
Posts: 4
Joined: 22 Dec 2020, 13:27

Re: Excel - Highlighting (underlining) the active row

23 Dec 2020, 10:43

@haichen Thanks I will try to do it (Yes, there is already a frame around the active cell but it's too thin)
daywalker
Posts: 23
Joined: 18 Jun 2019, 01:37

Re: Excel - Highlighting (underlining) the active row

21 Jan 2021, 07:41

Hi Haichen,

i have made this two changes

1) In case ComObjActive fails, SplashImage should be resetted:

Code: Select all

	try
		xl := ComObjActive("Excel.Application")
	catch
	{
		SplashImage, off
		wold:=-1
		return
	}
}
2) Hiding Splash when you are in File Menu (Office 365):

Code: Select all

if hwnd:=WinActive("ahk_class XLMAIN")
{
	run:=1
	
	try
	{
		c:=xl.selection.rows.count  ; counts the lines of the selection
		y1:=xl.ActiveWindow.ActivePane.PointsToScreenPixelsY(xl.selection.offset(c,0).top) ; screen position of the selection shifted by its number of lines
		WinGetPos, x, Cy, Width, Height, ahk_id %hwnd%
		ControlGetFocus, WhichControl, ahk_id %hwnd%
		;MouseGetPos, , , WhichWindow, WhichControl ; Alternativly
		if (WhichControl="NetUIHWND1")
		{
			SplashImage, off
			wold:=-1
			return
		}
			
		if (yold <> y1) or (xold <> x) or (hold <> height) or (wold <> width)
		{
			r:="a" round(xl.ActiveWindow.ActivePane.ScrollRow //1,0)
			yr:=xl.ActiveWindow.ActivePane.PointsToScreenPixelsY(xl.range(r).top)-10	
											
			if (y1<yr) or (y1>(cy+height-20))
			{
				SplashImage, off
				return
			}
			
			Splashimage,, B W%Width% H%line% Y%y1% X%x% CW%color% ; create line only on change
			yold:=y1
			wold:=width
			hold:=height
			xold:= x
		
		}
	}
}
User avatar
haichen
Posts: 592
Joined: 09 Feb 2014, 08:24

Re: Excel - Highlighting (underlining) the active row

21 Jan 2021, 10:25

Hi daywalker, thanks for your error correction and improvement.
This is really useful.
pallabibaruah18
Posts: 14
Joined: 20 Jan 2021, 23:20

Re: Excel - Highlighting (underlining) the active row

23 Jan 2021, 06:03

Used the script. It worked fine

Return to “Scripts and Functions”

Who is online

Users browsing this forum: jacek678 and 26 guests