Excel - Highlighting (underlining) the active row

[haichen]

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 (93.34 KiB) Viewed 2925 times

Edit 24.01.2021: Improvement by daywalker

[burque505]

@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

Code: Select all

; Excel_Get by jethrow (modified)
; Forum:    https://autohotkey.com/boards/viewtopic.php?f=6&t=31840
; Github:   https://github.com/ahkon/MS-Office-COM-Basics/blob/master/Examples/Excel/Excel_Get.ahk
Excel_Get(WinTitle:="ahk_class XLMAIN", Excel7#:=1) {
    static h := DllCall("LoadLibrary", "Str", "oleacc", "Ptr")
    WinGetClass, WinClass, %WinTitle%
    if !(WinClass == "XLMAIN")
        return "Window class mismatch."
    ControlGet, hwnd, hwnd,, Excel7%Excel7#%, %WinTitle%
    if (ErrorLevel)
        return "Error accessing the control hWnd."
    VarSetCapacity(IID_IDispatch, 16)
    NumPut(0x46000000000000C0, NumPut(0x0000000000020400, IID_IDispatch, "Int64"), "Int64")
    if DllCall("oleacc\AccessibleObjectFromWindow", "Ptr", hWnd, "UInt", -16, "Ptr", &IID_IDispatch, "Ptr*", pacc) != 0
        return "Error calling AccessibleObjectFromWindow."
    window := ComObject(9, pacc, 1)
    if ComObjType(window) != 9
        return "Error wrapping the window object."
    Loop
        try return window.Application
        catch e
            if SubStr(e.message, 1, 10) = "0x80010001"
                ControlSend, Excel7%Excel7#%, {Esc}, %WinTitle%
            else
                return "Error accessing the application object."
}

; References
;   https://autohotkey.com/board/topic/88337-ahk-failure-with-excel-get/?p=560328
;   https://autohotkey.com/board/topic/76162-excel-com-errors/?p=484371
;   https://autohotkey.com/boards/viewtopic.php?p=134048#p134048

Regards, and thanks!
burque505

[haichen]

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]

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

[DataLife]

Worked great on first try.

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

thanks

[hasantr]

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.

[haichen]

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.

[hasantr]

Can slight delay be corrected? Using ~Lbutton instead of a timer. I tried little and gave up quickly because I didn't have time

[haichen]

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]

It works just great on first try.

[kczx3]

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
    }
}

[haichen]

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.

[kczx3]

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.

[haichen]

Yes, I also find it strange. Let's see if I find out something after

[SeeYouAtTop]

@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.

[haichen]

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]

@haichen Thanks I will try to do it (Yes, there is already a frame around the active cell but it's too thin)

[daywalker]

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
		
		}
	}
}

[haichen]

Hi daywalker, thanks for your error correction and improvement.
This is really useful.

[pallabibaruah18]

Used the script. It worked fine