Onkey Key Codes for ADD and SUBTRACT keys in earlier Excel version

[FlyBoy]

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.

 

[Bill Renaud]

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/library/ft8xdf67(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.

 

[Bill Renaud]

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

 

[FlyBoy]

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.

 

[Bill Renaud]

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