Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Onkey Key Codes for ADD and SUBTRACT keys in earlier Excel version
I am programming a data entry worksheet for my employer. I use Excel 2004
for Mac, they still have 2000 for Windows. I have spent a lot of time coding so things will work on both platforms, but this one has me stumped. I am using the Onkey function to trap the use of the Add key on the numeric keypad to use as an incremental spinner-like key for quick data entry -- each tap of the key increments the time in a field by 5 minutes. The Key Code for the Onkey function in 2004 is "{+}" and works as intended. But the 2000 version isn't making the reassignment and just enters a "+". If there is a Key Code for this in Excel 2000, what is it? If not, how else can I accomplish this functional feature. The Subtract Key would be nice, too. I'm stuck on this and I need some big help. Can't find anything about these on MSDN, etc. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Onkey Key Codes for ADD and SUBTRACT keys in earlier Excel version
I use Excel 2000 (SP-3) and got the following to work:
'---------------------------------------------------------------------- Public Sub KeysOn() Application.OnKey "{107}", "IncrementCell" Application.OnKey "{109}", "DecrementCell" End Sub '---------------------------------------------------------------------- Public Sub KeysOff() Application.OnKey "{107}" Application.OnKey "{109}" End Sub '---------------------------------------------------------------------- Public Sub IncrementCell() With ActiveCell .Value = .Value + 1 End With End Sub '---------------------------------------------------------------------- Public Sub DecrementCell() With ActiveCell .Value = .Value - 1 End With End Sub I found the following Microsoft web page had a listing of all of the keyboard codes: Key Code Constants (ActiveX Controls) http://msdn2.microsoft.com/en-us/lib...67(vs.71).aspx To read these tables, use the Description column on the right to find the key you are interested in, then get the value from the middle column ("Value"). It will be in hexadecimal, so strip off the "&H" part on the left side, then convert the remaining characters from HEX to Decimal. So, for example: Constant Value Description ------------- ----- ---------------------- vbKeyAdd &H6B PLUS SIGN (+) key vbKeySubtract &H6D MINUS SIGN (-) key For the + key: 6B HEX is 107 decimal. For the - key: 6D HEX is 109 decimal. -- Regards, Bill Renaud |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Onkey Key Codes for ADD and SUBTRACT keys in earlier Excel version
OK, I should have checked Visual Basic Help first! It lists the Keycode
Constants in the Constants chapter of Help. vbKeyAdd has a value of 0x6B, and vbKeySubtract has a value of 0x6D. Revised code shown below: '---------------------------------------------------------------------- Public Sub KeysOn() Application.OnKey "{" & vbKeyAdd & "}", "IncrementCell" Application.OnKey "{" & vbKeySubtract & "}", "DecrementCell" End Sub '---------------------------------------------------------------------- Public Sub KeysOff() Application.OnKey "{" & vbKeyAdd & "}" Application.OnKey "{" & vbKeySubtract & "}" End Sub '---------------------------------------------------------------------- Public Sub IncrementCell() With ActiveCell .Value = .Value + 1 End With End Sub '---------------------------------------------------------------------- Public Sub DecrementCell() With ActiveCell .Value = .Value - 1 End With End Sub -- Regards, Bill Renaud |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Onkey Key Codes for ADD and SUBTRACT keys in earlier Excel ver
Outstanding, thank you. And so quick, too.
Yes, this works for me also. I find the converted-to-decimal type of key argument easier to code. I'd have to look up the VBA constant just as much as the Hex code anyway. I too found the table of Key Code Constants, but the VBA Language Reference gave me no clue that you could specify the Key argument in any other way than those listed. You put the two together for me, that's the tip I needed. It has never failed that someone out there knows the answer to my question. I need to remember to do this first before spending hours trying to figure it out. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Onkey Key Codes for ADD and SUBTRACT keys in earlier Excel ver
<<It has never failed that someone out there knows the answer to my
question. I need to remember to do this first before spending hours trying to figure it out. Glad to help. Actually, I figure I may need these techniques someday in my own programming, so I am building a "library" of knowledge, techniques, and routines that I will be able to use in the future myself! -- Regards, Bill Renaud |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
countifs on earlier version of excel | Excel Worksheet Functions | |||
Last saved by earlier version of Excel | Excel Discussion (Misc queries) | |||
Can I save as an earlier version of excel? | Excel Discussion (Misc queries) | |||
Recover earlier version of excel sheet after new version saved? | Excel Discussion (Misc queries) | |||
how do i see an earlier saved version of an excel file | Excel Discussion (Misc queries) |