Using Application.onkey in an if statement

The UK's most regular instructor-led training courses.
Training information: 2003 access vba online classes · Excel london

Resolved · Medium Priority · Version 2003

6 posts

ReplyMon 1 Mar 2010, 10:27Delegate Alex said...

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

452 posts

ReplyMon 1 Mar 2010, 16:48Trainer Anthony said...

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

Mon 8 Mar 2010: Automatically marked as resolved.

· Introduction to Visual Basic for Applications
· How VBA Can Add Power To Your PowerPoint Presentations
· What Is Microsoft Excel VBA and How Can It Help You Work Smarter?
· Make Office work for you with VBA
· Trigger Happy In Excel VBA

Please browse our web site to find out more about
2003 access vba online classes and other Microsoft training courses.

Vba tip:

Use GoTo to Select A Cell With VBA

To select a certain cell on a Worksheet you can use:

Application.Goto Reference:=Range("V300")

or more simply

Range("V300").Select

If, on the other hand, you want the selected cell to be the top/left cell on the screen you use:

Application.Goto Reference:=Range("V300"), Scroll=True

View all VBA hints and tips