I just bought a USB Numerical Keypad and was hoping to use it to enter times in excel. I'm planning to remap either the '00' or '*' button on the external kepad to input ":" in order to quickly enter times (hh:mm:ss), but i'm coming across an issue in excel. If there isn't anything entered into a cell, either button will be replaced with the desired ":", which is almost useful. But if there is already something entered in the cell, like the first two digits in a time , the original value of '00' or '*' will be entered instead of the desired ":". Is there anyway to make my simple hotkey script work with entering times in excel?
:*:00:::
:*:*:::
Thanks!
Simple numpad remapping problem in excel.
-
- Posts: 1
- Joined: 15 Jan 2019, 01:43
Re: Simple numpad remapping problem in excel.
Sorry for the slow response my plane came as I was about to reply.
In Excel almost nothing is simple
While this seems a little overkill, it does work for me using COM.
The upside is it's more reliable and also works in the backgroundPlease don't let the functions scare you off and ask if you have any questions.
In Excel almost nothing is simple
While this seems a little overkill, it does work for me using COM.
The upside is it's more reliable and also works in the background
Code: Select all
xlObj := ComExcelInit()
date := "01/01/2019"
:*:00::
ControlSend,, {esc}, ahk_class XLMAIN
CellObj := xlObj.Selection
CellObj.NumberFormat := "dd:mm:yy"
xlObj.Cells( CellObj.row, CellObj.column ).value := date
return
; ###############
; ## functions ##
; ###############
ComExcelInit()
{
try
{
return ComExcelConnect( "ahk_class XLMAIN" ).Application
}
catch
{
msgbox, 0x10, whoops!, Error connecting to Excel!
ExitApp
}
}
ComExcelConnect( WinTitle )
{
ControlSend,, {esc}, ahk_class XLMAIN
objID := "-16", objID &= 0xFFFFFFFF
refID := -VarSetCapacity( iid, 16 )
iid_type := NumPut( 0x46000000000000C0
, NumPut( 0x20400, iid, "Int64" )
, "Int64")
ControlGet, hwnd, hwnd, , EXCEL71, % WinTitle
DllCall( "oleacc\AccessibleObjectFromWindow", ptr, hwnd, uInt, objID, ptr, refID+iid_type, "ptr*", pObj )
return ComObjEnwrap( 9, pObj )
}
Re: Simple numpad remapping problem in excel.
I wasn't successful making the 00 into a hotstring because the first 0 erases the contents of the cell. WIth more work I could probably figure it out but I'll leave that to you, an alternative may be to see if the "00" key registers as a special key, and assign it as a hotkey instead of a hotstring.
AHK Special Keys Assignments
A little slimmer of an answer using Excel COM and Edit controls using * as an auto-hotstring:
It will work whether you are actively editing inside the cell or not passively selecting the cell.
Let me know how this works for you!
AHK Special Keys Assignments
A little slimmer of an answer using Excel COM and Edit controls using * as an auto-hotstring:
Code: Select all
#SingleInstance, force
SendMode, Input
:?*:*:: ; Sets "*" as hotstring - executes upon initial keypress even if preceded by an alphanumeric character (aka inside of another word or string of numbers)
try Xl := ComObjActive("Excel.Application") ; Try to connect to Active Excel instance
try Xl_Hwnd := Xl.HWND ; Try to set unique Window Handle to a variable
ControlSend, EXCEL71, {F2}, ahk_id %Xl_Hwnd% ; Move Edit Control Focus to Excel's formula bar (aka editing inside the cell)
Send, : ; Send a colon
return
Let me know how this works for you!
-TL
- Thoughtfu1Tux
- Posts: 125
- Joined: 31 May 2018, 23:26
Re: Simple numpad remapping problem in excel.
Why not do a direct remap of the key to the key you want it to output?
Code: Select all
NumpadMult::send,{:}
Re: Simple numpad remapping problem in excel.
Because the OP stated that they need to do this within Excel and Excel has it's quirks that you must work around / work with. Also, they said wanted to use a hotstring.Thoughtfu1Tux wrote: ↑22 Jan 2019, 23:51Why not do a direct remap of the key to the key you want it to output?
Code: Select all
NumpadMult::send,{:}
Your solution would not be reliable because the OP needs the ":" character each time to be entered in each cell without resetting/clearing the active contents of the cell, instead appending the ":" character to the end of what is currently there (if anything).
Test out our different solutions, and you will see the differences.
-TL
- Thoughtfu1Tux
- Posts: 125
- Joined: 31 May 2018, 23:26
Re: Simple numpad remapping problem in excel.
Excel does have its quirks, but it doesn't have any issues with a key remap like i mentioned in the post above. I do some bookkeeping and time card management for my company, and i remap keys to help put in the colon using the keypad all the time.
I tested the function you posted and my function, and they work equally well, I think mine is just simpler and easier to use. And if you use #ifwinactive it will only activate within Excel.
OP only has 1 post in his history, so he's probably very new to AHK and maybe didn't realize that a key remap would be simpler to do than a hotstring.
Code: Select all
#IfWinActive, ahk_class XLMAIN ; Only activates if excel is open
NumpadMult::send,{:} ; When you press the Nupad Multiply key, AHK will catch command and send a {:} instead.
#IfWinActive
Re: Simple numpad remapping problem in excel.
Hi Thoughtfu1Tux,
I like the simplicity of your solution. However, my only qualm is that if I have contents in my cell, programming the hotkey in such a way will discard the contents instead of appending it to the end of the cell's contents.
Code: Select all
active cell --> [these are the cell contents]
press hotkey --> [:]
Code: Select all
active cell --> [these are the cell contents]
press hotkey --> [these are the cell contents:]
We could combine our approaches to make something that is potentially a little more flexible without using COM:
Code: Select all
SendMode, Input
#IfWinActive, ahk_class XLMAIN ; Only activates if Excel window is in Active Focus
NumpadMult::
ControlSend, EXCEL71, {F2}, ahk_class XLMAIN ; Move Edit Control Focus to Excel's formula bar (aka editing inside the cell without erasing contents)
Send : ; Send a colon
return
-TL
Who is online
Users browsing this forum: No registered users and 339 guests