How to determine if I key is being pressed in VBA

[Mightyginger]

Okay, so say I want to import a file from a location into my spreadsheet. Part of this involves copying some numbers across. Thats fine. Now, what I'd like to be able to do is hold down a key while the macro is running (Alt doesn't seem to affect the macro running at the moment, so I can run it while pressing Alt and it runs fine) and somewhere in the code check to see if that specific (or maybe any) key is being pressed. Then the spreadsheet might give the inverse of that number depending if the number is being pressed or not.

Can I do that?

 

[xlbo]

How about a simple IF statement

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

if myAnswer = vbyes then
  'code to invert numbers
else
  'don't invert
end if

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[Mightyginger]

Totally agree, reasonable solution. But is it possible to do what I'm looking to do?

 

[xlbo]

Why do it if there is a very simple solution ??

My initial reaction would be "probably not" as VBA is a modal language (like modal forms, they can't really be interrupted except by stopping the cod) but every time I say that, someone just has to prove me wrong ;-)



Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[Mightyginger]

Okay, I agree your point but I was just interested if it could be done. Thats all. I wondered if someone here might have done it before.

 

[xlbo]

That's why I put the 2nd bit - every time I say something can't be done, someone proves me wrong - so - there it is.

I don't think this can be done



Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[Mightyginger]

I actually found this on the web. This would ideal, as I can just hold down shift and click the button. It doesn't seem to work though.

Dim ShiftKeyPress As Boolean

Private Sub CommandButton1_Click()
If ShiftKeyPress Then
MsgBox "Shift key is pressed"
Else
MsgBox "Shift key is not pressed"
End If

End Sub

Private Sub CommandButton1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)

ShiftKeyPress = True
End Sub

Private Sub CommandButton1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)

ShiftKeyPress = False
End Sub


Pinched from here ->

http://www.msusenet.com/archive/index.php/t-509270.html

Any ideas guys?

 

[SkipVought]

"I actually found this on the web."

Do you realize that, out of ALL the stuff that you can find on the web, only a small percantage is reliable?

First, as Geoff has suggested, a simple If statment would most likely do just fine.

Second, there are fewer events that can be trapped on a WORKSHEET than on an ActiveX Control. The KeyUp & KeyDown events are assoviated with CONTROLS and not SHEETS.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[TonyJollans]

Sorry, Geoff

I agree this is definitely not the right way to code for this situation, but you can test the state of a key using the code below. I have never tried to incorporate something like this into an application but it ought to work.

Run this code (it only takes a few seconds) and while it is running, press the "I" key to see the message box pop up:

[blue]Declare Function GetAsyncKeyState _
                Lib "user32" _
                (ByVal vKey As Long) _
        As Integer
        
Sub KeyCheck()
    For i = 1 To 10000000
        If GetAsyncKeyState(Asc("I")) <> 0 Then MsgBox i
    Next
End Sub[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[xlbo]

'sok Tony - as per my post, I was expecting someone to prove me wrong ! To be honest, I thought it might well be you too !

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[ETID]

Can you leverage this?....

'In any module

Sub test_it()
MsgBox ("This message brought to you by the letter Capitol X")
End Sub


'In the workbook open event

Private Sub Workbook_Open()
Application.OnKey "X", "test_it"
End Sub