Capturing Keypresses and Pausing Code? Advice required.

[Stargazer]

Hiya,

I'm trying to get a small project working but am struggling to make it do exactly what I want. Having completely run out of talent, I feel it is time I ask for some assistance.

As a Flight Simmer, I often have lots of procedures to work through. Though I know a lot of what I need to do, I'd still like to have an interactive checklist to make sure that I get it right. Kind of a virtual co-pilot.

The worksheet contains several columns of data. Each column is a different checklist. Each line in the column contains an entry that I need to pay attention to.

So far, I have written code that does easy stuff like stepping down the checklist reading out the items and when it gets to the last entry, moves to the top line of the next column until all entries have been read.

The tricky bit I'm having trouble with is getting it act like a co-pilot.

The ideal behaviour would be for it to read out the command and then wait for my 'Check' signal (which I'll come back to in a moment). However, the only ways I know to make the code halt and await input from a user, is to show a message box that has to be OK'd to continue which is far from ideal, or use application.wait which puts the whole of excel into lockdown for a set amount of time and then resumes regardless.

As for the 'Check' signal I need to give to make it move on, I was hoping originally to use a voice interface so I can use my headset and give a verbal "Check" or "Set", however, this is well beyond me and I've had trouble finding much about it on the internet.

I have programmed a keypress recognition event, however, the problem with this is that excel needs to be the active application for it to work. If I have flight sim open and I'm adjusting knobs and pressing buttons in the cockpit, it would be an absolute ballache to keep switching back and forth from the 3D engine to the Workbook.

Flight Sim is also a bit of a cow with regards to full screened graphics and alt-tabbing and I wouldn't even dare attempt to create a userform that attempts to stay top-most. Anything that takes the focus off the Sim is likely to cause severe side effects.

So to sum it up, the two questions in brief are:-

1) How to pause the code without message boxing or application.wait so that it will only continue on my input.

2) How to make Excel listen for keypress events at a system level instead of an application one.

I have some neat ideas on how to avoid the awful Microsoft Sam (and other similar) voice and can code them if I can overcome these two hurdles, but these ones have me properly stumped.

Any and all advice is greatly appreciated,

Kind regards,

Rob.

[Kenneth Hobs]

See if this concept helps. Add it to a Module and run the Check routine from a blank worksheet.

After running, open an application like NotePad and press spacebar after the Check1 or Check2 and it will tell you if it was pressed and update the offset cell next to that cell's value that was spoken.

[vba]Option Explicit

' https://www.informit.com/articles/ar...66892&seqNum=4

Private Declare Function GetKeyState Lib "user32" _
(ByVal vKey As Long) As Integer

'Private Const VK_SHIFT As Long = &H10
'Private Const VK_CONTROL As Long = &H11
'Private Const VK_MENU As Long = &H12
Private Const VK_CAPITAL = &H14
Private Const VK_NUMLOCK = &H90
Private Const VK_SCROLL = &H91

Public Enum GetKeyStateKeyboardCodes
'gksKeyboardShift = VK_SHIFT
'gksKeyboardCtrl = VK_CONTROL
'gksKeyboardAlt = VK_MENU
gksKeyboardCapsLock = VK_CAPITAL
gksKeyboardNumLock = VK_NUMLOCK
gksKeyboardScrollLock = VK_SCROLL
gksKeyboardSpace = vbkeyspace
End Enum

Public Function IsKeyPressed _
(ByVal lKey As GetKeyStateKeyboardCodes) As Boolean

Dim iResult As Integer

iResult = GetKeyState(lKey)

Select Case lKey
Case gksKeyboardCapsLock, gksKeyboardNumLock, _
gksKeyboardScrollLock

'For the three 'toggle' keys, the 1st bit says if it's
'on or off, so clear any other bits that might be set,
'using a binary AND
iResult = iResult And 1

Case Else
'For the other keys, the 16th bit says if it's down or
'up, so clear any other bits that might be set, using a
'binary AND
iResult = iResult And &H8000
End Select

IsKeyPressed = (iResult <> 0)

End Function



Sub Check()
Dim c As Range, r As Range, t As Double, tf As Boolean

Range("A1").Value = "Check1"
Range("A2").Value = "Check2"
Set r = Range("A1", Range("A" & Rows.Count).End(xlUp))
r.Offset(, 1).Value = ""

Application.Wait (Now + TimeValue("00:00:02"))

With Application.Speech
For Each c In r
.Speak c.Value, , , True
t = Timer
tf = False
Do
If IsKeyPressed(gksKeyboardSpace) Then
.Speak "Spacebar was pressed.", , , True
c.Offset(, 1).Value = True
tf = True
End If
If Timer >= t + 5 Then tf = True
Loop Until tf
Next c
End With
End Sub
[/vba]

[Stargazer]

Many thanks for the reply... I'll stick this into my checklist tonight and see what happens. I'll report back tomorrow or Monday with either gushing thanks or an admittance of my own stupidity.

[Stargazer]

Hi,

I've just put this code in and I'm afraid I have firther questions.

Firstly, it was removing a number of existing checklist values so I removed the 4 lines from between the Dim and Application.Wait in the 'Check' sub. However, this then causes other elements to stop working. If I just leave in the 3rd and 4th lines that seem to be required, a big section of the column B checklist gets nuked.

If I only leave in the 3rd line, it does it's thing but the column B checklist gets replaced with a number of 'True' values (more on that in a moment).

The structure of your code is quite unlike anything I've written so I'm not hugely confident tinkering with it as I feel I'd probably just make things worse. Do you have any pointers? Perhaps if I was to merge some of the logic in your routine with a 'For x = 1 to LastRow Step 1' sort of thing? I'm awesome with simple step-through's.

Secondly, the checklist values do not wait for my input, but rather just keep reading out one after the other. For a take-off scenario, this would be a nightmare if I have to queue for take-off and my co-pilot is already going through the in-flight checklists.

However, Excel is now capturing the Spacebar event from other windows which is (in my opinion anyway) nothing short of miraculous. I'm guessing I now need to port some of your logic over into my step through to make my co-pilot step down through the lists as I have it doing currently, but with your code listening for my keypress. How much of it do I take and which bits serve only for the example?

I''ll show this to the guy I work with tomorrow... He'll be quite impressed by it also and I already know he'll have about a dozen uses for it, so a big thanks for your help thus far and an anticipatory thanks from my mate in the office.

Kind regards,

Rob.

[Kenneth Hobs]

The code already loops using For Each, rather than For x=1 to whatever. Each cell in the range of A1 to the last cell with a value in a cell of column A is iterated for you.

If you want it to loop until you get a spacebar key press or another without a timeout, that can be be done. If you make it look for another key, that could be a False in the offset column.

Of course if you get a False, you might want to reloop at the end or repeat and wait until you get all True. The logic is up to you.

I am not sure what removing lines would impact. It is best to post code. Of course some lines can be removed as they are not relevant to your needs.

State logic for your goal and then code can be created.