CODEWITHSUNDEEP
excelautohotkey
Moe
Moe

Reputation: 11

AHK in MS Excel (Cell Selection Issue With Script)

I started a little script using CAPSLock key for navigation (This is only part of the code):

SetCapsLockState, AlwaysOff
CapsLock & i::
    if getkeystate("alt") = 0
            Send,{Up}
    else
            Send,+{Up}
return
CapsLock & l::
    if getkeystate("alt") = 0
            Send,{Right}
    else
 send, +{Right}
return

It works perfectly everywhere.. except in MS Excel! When I select a range of cells the selection marquee is there but no cell reference is taken in the formula. Ex: Summing C3:C10 turns into =sum() no cells are actually selected in the formula. if I keep trying up and down many times.. it shows, but never consistent.

Any idea how to fix this?

Thank you in advance

Upvotes: 1

Views: 491

Answers (1)

0x464e
0x464e

Reputation: 6489

The problem comes from your shift key being released between the key presses.
Like for example try pressing keys like this in Excel and you'll see you're not actually selecting cells, you're just highlighting them:

Shift Down, Up Arrow, Shift Up
Shift Down, Up Arrow, Shift Up
Shift Down, Up Arrow, Shift Up
...

So you're going to have to keep shift pressed for the whole duration of your selection making process.
Here's what I could come up with:

CapsLock::Shift

#If, GetKeyState("CapsLock", "P")
*i::
    if (GetKeyState("Alt", "P"))
        SendInput, {Blind}{Alt Up}{Up}
    else
        SendInput, {Up}
return

*l::
    if (GetKeyState("Alt", "P"))
        SendInput, {Blind}{Alt Up}{Right}
    else
        SendInput, {Down}
return
#If

Or just like this to write it a lot cleaner with a ternary:

CapsLock::Shift

#If, GetKeyState("CapsLock", "P")
*i::SendInput, % GetKeyState("Alt", "P") ? "{Blind}{Alt Up}{Up}" : "{Up}" 
*l::SendInput, % GetKeyState("Alt", "P") ? "{Blind}{Alt Up}{Right}" : "{Right}"
#If

So what's happening?
First we use the simple remapping syntax(docs) to remap CapsLock to Shift.

Then we create context sensitive hotkeys for I and L with #If(docs).
The hotkeys are only triggered if the physical state of CapsLock is down.
We need the physical state, because the logical state (which is default) was remapped to shift.

The hotkeys are created with the * modifier(docs) so they fire even if Shift and/or Alt is held down.

I switched the Send commands SendInput due to it being the recommended faster and more reliable send mode(docs).
And then about {Blind}. Normally, when you just send something with a send command, it automatically releases any modifiers you may be holding. But if you use the blind send mode(docs), this doesn't happen. So Shift is still held down all the time.

Upvotes: 1

Related Questions