How to detect a key press while running a sub

[Mightyginger]

Hi,

I have a sub that imports a text file with some numbers. I want to be able to see if a key is pressed - if it is then it's going to invert the numbers. So is there some way that basically someone can press and hold down, say, the Shift key and then click a button to run the macro and I can then detect that key being pressed?

Thanks,


Neil.

 

[xlbo]

How about just a simple choice at the start of the macro?

myAnswer = msgbox("Do you want to invert the numbers?",vbyesno)

if myAnswer = vbYes then 
  'do your code to invert your numbers
else
  'don't invert numbers
end if

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Mightyginger]

Yes, agreed, that would be easier however we do a lot of importing of these files and only a handful need to be imported so a solution to the original thing would be really handy!!!

Sorry!!!


Neil.

 

[xlbo]

what is the business case for this ??

surely it is better to be prompted rather than relying on someone to know / remember to press a certain key down?

how are they going to know when to press the key.
what about when you get new starters ?

There are so many issues with this approach - I cannot believe that a business would accept it as a risk just to save a very small amount of time

Why not just have 2 subs - import and import reverse??

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Mightyginger]

We're importing risk for trades we do. In most cases the risk is correct however in certain circumstances the customer wishes to unwind the trade so the risk that is imported is the wrong way round as the customer has now unwound and so the trade is the other way round. It's pretty obvious in the spreadsheet if its the wrong way round so making an error importing will be highly unlikely but if it were to happen would be obvious.

I assure you there is a business case for this.

Can you help please?

Thanks,


Neil.

 

[xlbo]

why are you close minded about this? There are other, simpler and IMHO better options to do something like this.

Having said that, a quick search of this forum for "key press" throws up the following thread:

thread707-889282



Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Mightyginger]

Geoff, firstly thanks for your help and pointing me in the correct direction. I would like to think I'm not closed minded regarding what I'm looking to do but simply just to say, without writing an essay about exactly what i'm doing, that this suits the purpose precisely.

Anyway, for anyone following this thread, or stumbling across it at a later date, here is some code that will do the job. Below the code are the values you need for each of the different keys. You just need to change the code at the top "Const ReqKey As Long = " equals to the code you want.


Neil.

Code: (I'm using Shift)

Declare Function GetAsyncKeyState Lib "User32.dll" (ByVal vKey As Long) As Long

Const ReqKey As Long = &H10

Sub is_correct_key_pressed()

If GetAsyncKeyState(ReqKey) <> 0 Then
run_sub_of_your_choice
End If

End Sub

Key codes:

VK_LBUTTON (&H01)
Left mouse button

VK_RBUTTON (&H02)
Right mouse button

VK_CANCEL (&H03)
Control-break processing

VK_MBUTTON (&H04)
Middle mouse button (three-button mouse)

VK_XBUTTON1 (&H05)
Windows 2000/XP: X1 mouse button

VK_XBUTTON2 (&H06)
Windows 2000/XP: X2 mouse button

- (&H07)
Undefined

VK_BACK (&H08)
BACKSPACE key

VK_TAB (&H09)
TAB key

- (&H0A-0B)
Reserved

VK_CLEAR (&H0C)
CLEAR key

VK_RETURN (&H0D)
ENTER key

- (&H0E-0F)
Undefined

VK_SHIFT (&H10)
SHIFT key

VK_CONTROL (&H11)
CTRL key

VK_MENU (&H12)
ALT key

VK_PAUSE (&H13)
PAUSE key

VK_CAPITAL (&H14)
CAPS LOCK key

VK_KANA (&H15)
Input Method Editor (&HIME) Kana mode

VK_HANGUEL (&H15)
IME Hanguel mode (maintained for compatibility; use VK_HANGUL)

VK_HANGUL (&H15)
IME Hangul mode

- (&H16)
Undefined

VK_JUNJA (&H17)
IME Junja mode

VK_FINAL (&H18)
IME final mode

VK_HANJA (&H19)
IME Hanja mode

VK_KANJI (&H19)
IME Kanji mode

- (&H1A)
Undefined

VK_ESCAPE (&H1B)
ESC key

VK_CONVERT (&H1C)
IME convert

VK_NONCONVERT (&H1D)
IME nonconvert

VK_ACCEPT (&H1E)
IME accept

VK_MODECHANGE (&H1F)
IME mode change request

VK_SPACE (&H20)
SPACEBAR

VK_PRIOR (&H21)
PAGE UP key

VK_NEXT (&H22)
PAGE DOWN key

VK_END (&H23)
END key

VK_HOME (&H24)
HOME key

VK_LEFT (&H25)
LEFT ARROW key

VK_UP (&H26)
UP ARROW key

VK_RIGHT (&H27)
RIGHT ARROW key

VK_DOWN (&H28)
DOWN ARROW key

VK_SELECT (&H29)
SELECT key

VK_PRINT (&H2A)
PRINT key

VK_EXECUTE (&H2B)
EXECUTE key

VK_SNAPSHOT (&H2C)
PRINT SCREEN key

VK_INSERT (&H2D)
INS key

VK_DELETE (&H2E)
DEL key

VK_HELP (&H2F)
HELP key

(&H30)
0 key

(&H31)
1 key

(&H32)
2 key

(&H33)
3 key

(&H34)
4 key

(&H35)
5 key

(&H36)
6 key

(&H37)
7 key

(&H38)
8 key

(&H39)
9 key

- (&H3A-&H40)
Undefined

(&H41)
A key

(&H42)
B key

(&H43)
C key

(&H44)
D key

(&H45)
E key

(&H46)
F key

(&H47)
G key

(&H48)
H key

(&H49)
I key

(&H4A)
J key

(&H4B)
K key

(&H4C)
L key

(&H4D)
M key

(&H4E)
N key

(&H4F)
O key

(&H50)
P key

(&H51)
Q key

(&H52)
R key

(&H53)
S key

(&H54)
T key

(&H55)
U key

(&H56)
V key

(&H57)
W key

(&H58)
X key

(&H59)
Y key

(&H5A)
Z key

VK_LWIN (&H5B)
Left Windows key (Microsoft® Natural® keyboard)

VK_RWIN (&H5C)
Right Windows key (Natural keyboard)

VK_APPS (&H5D)
Applications key (Natural keyboard)

- (&H5E)
Reserved

VK_SLEEP (&H5F)
Computer Sleep key

VK_NUMPAD0 (&H60)
Numeric keypad 0 key

VK_NUMPAD1 (&H61)
Numeric keypad 1 key

VK_NUMPAD2 (&H62)
Numeric keypad 2 key

VK_NUMPAD3 (&H63)
Numeric keypad 3 key

VK_NUMPAD4 (&H64)
Numeric keypad 4 key

VK_NUMPAD5 (&H65)
Numeric keypad 5 key

VK_NUMPAD6 (&H66)
Numeric keypad 6 key

VK_NUMPAD7 (&H67)
Numeric keypad 7 key

VK_NUMPAD8 (&H68)
Numeric keypad 8 key

VK_NUMPAD9 (&H69)
Numeric keypad 9 key

VK_MULTIPLY (&H6A)
Multiply key

VK_ADD (&H6B)
Add key

VK_SEPARATOR (&H6C)
Separator key

VK_SUBTRACT (&H6D)
Subtract key

VK_DECIMAL (&H6E)
Decimal key

VK_DIVIDE (&H6F)
Divide key

VK_F1 (&H70)
F1 key

VK_F2 (&H71)
F2 key

VK_F3 (&H72)
F3 key

VK_F4 (&H73)
F4 key

VK_F5 (&H74)
F5 key

VK_F6 (&H75)
F6 key

VK_F7 (&H76)
F7 key

VK_F8 (&H77)
F8 key

VK_F9 (&H78)
F9 key

VK_F10 (&H79)
F10 key

VK_F11 (&H7A)
F11 key

VK_F12 (&H7B)
F12 key

VK_F13 (&H7C)
F13 key

VK_F14 (&H7D)
F14 key

VK_F15 (&H7E)
F15 key

VK_F16 (&H7F)
F16 key

VK_F17 (&H80H)
F17 key

VK_F18 (&H81H)
F18 key

VK_F19 (&H82H)
F19 key

VK_F20 (&H83H)
F20 key

VK_F21 (&H84H)
F21 key

VK_F22 (&H85H)
F22 key

VK_F23 (&H86H)
F23 key

VK_F24 (&H87H)
F24 key

- (&H88-8F)
Unassigned

VK_NUMLOCK (&H90)
NUM LOCK key

VK_SCROLL (&H91)
SCROLL LOCK key

(&H92-96)
OEM specific

- (&H97-9F)
Unassigned

VK_LSHIFT (&HA0)
Left SHIFT key

VK_RSHIFT (&HA1)
Right SHIFT key

VK_LCONTROL (&HA2)
Left CONTROL key

VK_RCONTROL (&HA3)
Right CONTROL key

VK_LMENU (&HA4)
Left MENU key

VK_RMENU (&HA5)
Right MENU key

VK_BROWSER_BACK (&HA6)
Windows 2000/XP: Browser Back key

VK_BROWSER_FORWARD (&HA7)
Windows 2000/XP: Browser Forward key

VK_BROWSER_REFRESH (&HA8)
Windows 2000/XP: Browser Refresh key

VK_BROWSER_STOP (&HA9)
Windows 2000/XP: Browser Stop key

VK_BROWSER_SEARCH (&HAA)
Windows 2000/XP: Browser Search key

VK_BROWSER_FAVORITES (&HAB)
Windows 2000/XP: Browser Favorites key

VK_BROWSER_HOME (&HAC)
Windows 2000/XP: Browser Start and Home key

VK_VOLUME_MUTE (&HAD)
Windows 2000/XP: Volume Mute key

VK_VOLUME_DOWN (&HAE)
Windows 2000/XP: Volume Down key

VK_VOLUME_UP (&HAF)
Windows 2000/XP: Volume Up key

VK_MEDIA_NEXT_TRACK (&HB0)
Windows 2000/XP: Next Track key

VK_MEDIA_PREV_TRACK (&HB1)
Windows 2000/XP: Previous Track key

VK_MEDIA_STOP (&HB2)
Windows 2000/XP: Stop Media key

VK_MEDIA_PLAY_PAUSE (&HB3)
Windows 2000/XP: Play/Pause Media key

VK_LAUNCH_MAIL (&HB4)
Windows 2000/XP: Start Mail key

VK_LAUNCH_MEDIA_SELECT (&HB5)
Windows 2000/XP: Select Media key

VK_LAUNCH_APP1 (&HB6)
Windows 2000/XP: Start Application 1 key

VK_LAUNCH_APP2 (&HB7)
Windows 2000/XP: Start Application 2 key

- (&HB8-B9)
Reserved

VK_OEM_1 (&HBA)
Used for miscellaneous characters; it can vary by keyboard.
Windows 2000/XP: For the US standard keyboard, the ';:' key


VK_OEM_PLUS (&HBB)
Windows 2000/XP: For any country/region, the '+' key

VK_OEM_COMMA (&HBC)
Windows 2000/XP: For any country/region, the ',' key

VK_OEM_MINUS (&HBD)
Windows 2000/XP: For any country/region, the '-' key

VK_OEM_PERIOD (&HBE)
Windows 2000/XP: For any country/region, the '.' key

VK_OEM_2 (&HBF)
Used for miscellaneous characters; it can vary by keyboard.
Windows 2000/XP: For the US standard keyboard, the '/?' key


VK_OEM_3 (&HC0)
Used for miscellaneous characters; it can vary by keyboard.
Windows 2000/XP: For the US standard keyboard, the '`~' key


- (&HC1-D7)
Reserved

- (&HD8-DA)
Unassigned

VK_OEM_4 (&HDB)
Used for miscellaneous characters; it can vary by keyboard.
Windows 2000/XP: For the US standard keyboard, the '[{' key


VK_OEM_5 (&HDC)
Used for miscellaneous characters; it can vary by keyboard.
Windows 2000/XP: For the US standard keyboard, the '\|' key


VK_OEM_6 (&HDD)
Used for miscellaneous characters; it can vary by keyboard.
Windows 2000/XP: For the US standard keyboard, the ']}' key


VK_OEM_7 (&HDE)
Used for miscellaneous characters; it can vary by keyboard.
Windows 2000/XP: For the US standard keyboard, the 'single-quote/double-quote' key


VK_OEM_8 (&HDF)
Used for miscellaneous characters; it can vary by keyboard.

- (&HE0)
Reserved

(&HE1)
OEM specific

VK_OEM_102 (&HE2)
Windows 2000/XP: Either the angle bracket key or the backslash key on the RT 102-key keyboard

(&HE3-E4)
OEM specific

VK_PROCESSKEY (&HE5)
Windows 95/98/Me, Windows NT 4.0, Windows 2000/XP: IME PROCESS key

(&HE6)
OEM specific

VK_PACKET (&HE7)
Windows 2000/XP: Used to pass Unicode characters as if they were keystrokes. The VK_PACKET key is the low word of a 32-bit Virtual Key value used for non-keyboard input methods. For more information, see Remark in KEYBDINPUT, SendInput, WM_KEYDOWN, and WM_KEYUP

- (&HE8)
Unassigned

(&HE9-F5)
OEM specific

VK_ATTN (&HF6)
Attn key

VK_CRSEL (&HF7)
CrSel key

VK_EXSEL (&HF8)
ExSel key

VK_EREOF (&HF9)
Erase EOF key

VK_PLAY (&HFA)
Play key

VK_ZOOM (&HFB)
Zoom key

VK_NONAME (&HFC)
Reserved for future use

VK_PA1 (&HFD)
PA1 key

VK_OEM_CLEAR (&HFE)
Clear key

 

[fumei]

Sorry...and what is firing

Sub is_correct_key_pressed()

again???? Does this mean you are going to be constantly checking for keypresses?? Only when another macro is fired? Run this by again? If I understand correctly the idea is the user presses a key AND fires a macro. That macro checks what key is currently being held down? Is this idea?

How bizarre.

Gerry

 

[fumei]

Or...wait...does the user press the key after the macro sub is started? No, it is definitely that the user HOLDS a keys AND clicks a macro button. Right?

Gerry

 

[fumei]

Really? I just did some tests on this - as a design exercise. I have to agree with Geoff. This seems an odd interchange with a user regarding the stated purpose.

But hey...there sure is no accounting for the use of code. If it suits your concepts...then it does.

Gerry

 

[fumei]

No, wait. I don't get it. There are only three choices.


A) The user KNOWS it needs to be reversed
B) The user KNOWS it does NOT need to be reversed.
C) The user does not know either way.

The code simply requires a parameter of choice. Your concept is the code checks if the user is pressing an explicit key. If the user IS pressing it, then the values are reversed. If the user is NOT, then the values are NOT reversed.

C is the equivalent of B.....EXCEPT FOR the chance of a mistake. What if the user does NOT press the correct key...when they should have? What if the user presses the key...when they should not have?

This is th epoint that Geoff was making. There is NO explicitness to the end result. Yes, there is explicitness to checking for a key press. But there is NO way to explicitly determine prior to the execution of the instructions that the result is what is intended!

This is poor user interface.

As geoff suggested, two macros: one to import one way, the other to import in reverse.

Or get direct explicit information from the user.

I will categorically tell you that there WILL be a case when the user meant to hold that darn key...and didn't.

Gerry

 

[xlbo]

LOL - Gerry - is that what they call a brain dump ??

Neil - whilst I agree wholeheartedly with Gerry - if it suits your purpose then fair enough. We aren't there so I guess we don't know the whole story.

btw thanks for posting the code and the information on the key codes

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[strongm]

>the information on the key codes

it might be worth pointing out that VBA already has all the keycodes predefined (well, apart from a couple of the more obscure ones such as VK_HANGUEL) in the vbKeyCodeConstants enumeration

 

[fumei]

Geoff you are SO diplomatic!

Yeah, a brain dump. Kinda like those obscure dump files that Microsoft makes that even Microsoft doesn't know how to read.

My posts are probably as useful...

Gerry

 

[xlbo]

Gerry - depends what kinda day I'm having - you just don't see the non diplomatic ones 'cos they're RF'd very quickly !!

I hope you are joking about your posts btw.....

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question