Excel: get selection screen coordinates (selected cells)

[jeeswg]

Get selection screen coordinates and show a temporary border around the selection.

Code: Select all

q:: ;excel - get selection screen coordinates and show a temporary border around the selection (tested on Excel 2007)
;[Excel_Get function]
;excel and com? - Ask for Help - AutoHotkey Community
;https://autohotkey.com/board/topic/73940-excel-and-com/#entry469769
;[JEE_Borders function]
;Internet Explorer get element under cursor (show borders, show text) (any zoom percentage) - AutoHotkey Community
;https://autohotkey.com/boards/viewtopic.php?f=6&t=29458138430#p138430

oXl := Excel_Get()
vFactor := (oXl.ActiveWindow.Zoom / 100) * (A_ScreenDPI / 72)
vWinX := oXl.ActiveWindow.PointsToScreenPixelsX(0)
vWinY := oXl.ActiveWindow.PointsToScreenPixelsY(0)
vSelX := vWinX + oXl.Selection.Left * vFactor
vSelY := vWinY + oXl.Selection.Top * vFactor
vSelW := oXl.Selection.Width * vFactor
vSelH := oXl.Selection.Height * vFactor
oXl := ""
JEE_Borders(vSelX, vSelY, vSelW, vSelH)
return

;w:: ;A_CaretX and A_CaretY retrieve a coordinate near the cell
JEE_Borders(A_CaretX, A_CaretY, 100, 100)
return

Useful links:
How to get X Y screen coordinates of Excel cell/Range
https://www.mrexcel.com/forum/excel-que ... range.html

[JJohnston2]

Interesting idea. I like the proof of concept on translating screen coordinates, that may come in handy.

Functionally, this doesn't do so well when the selected cell is off-screen (which would go for any highlight mechanism, except for in this case, the code draws a highlight box on top of the ribbon for example).

I use the built-in highlighting function : ) Ctrl+A : )

More specifically, when searching for something (using the find/replace dialog), I trigger Ctrl+A after any kind of user selection change in the results list, so not only does the cell get highlighted as normal (with the normal selection box), but then the rest of the sheet goes dark so you can find the light/white cell on a fully darkened sheet. Works best on busy worksheets but a neat little trick since that's not really what Ctrl+A would normally be used for.

[cippalippa00]

Hi jeeswg and thank you for your script.
I have a problem with it: it doesn't work when there are freezed panes or row in the sheet. Is there some kind of workaround?

Thank you again

[cippalippa00]

I found a solution to my problem, in case anyone needs it. I just changed lines 11 and 12 to:

Code: Select all

vWinX := oXl.ActiveWindow.ActivePane.PointsToScreenPixelsX(0)
vWinY := oXl.ActiveWindow.ActivePane.PointsToScreenPixelsY(0)