Alex has attended:
Excel VBA Intro Intermediate course
Excel VBA Advanced course
Using Application.onkey in an if statement
Option Explicit
Dim bCancelPressed As Boolean
Private Sub Commandbutton1_Click()
bCancelPressed = False
Dim x As Integer
x = 1
Do While bCancelPressed = False
Selection.Cut
Selection.Offset(1, 0).Select
ActiveSheet.Paste
Selection.Name = ("mycell")
DoEvents
Application.Wait Now + TimeSerial(0, 0, 0.6)
Loop
End Sub
What i want to do is end the loop when the right key is pressed. I am using application.onkey to detect the whether the key has been pressed and that works fine outside the loop.
This is what i want to happen
If Appliction.onkey = Right Then
Exit Do
End If
Thanks
RE: Using Application.onkey in an if statement
Hi Alex, thanks for your query. The Onkey method does have its limitations and ideally you should be making a call to the Windows API in order to capture any keyboard input. The following code should do what you want:
***************
Option Explicit
Dim bCancelPressed As Boolean
Declare Function GetKeyState Lib "user32" (ByVal nVirtKey As Long) As Integer
Const VK_LEFT As Integer = &H25 'left
Sub test()
bCancelPressed = False
Dim x As Integer
Dim leftpressed As Boolean
x = 1
Do While bCancelPressed = False
Selection.Cut
Selection.Offset(1, 0).Select
ActiveSheet.Paste
Selection.Name = ("mycell")
If GetKeyState(VK_LEFT) < 0 Then leftpressed = True Else leftpressed = False
If leftpressed = True Then
bCancelPressed = True
Else
bCancelPressed = False
End If
DoEvents
Application.Wait Now + TimeSerial(0, 0, 0.6)
Loop
MsgBox "Stopped"
End Sub
*******************
Hope this helps,
Anthony
