KeyPresssed Eevent not being called

[dbaechtel]

I am trying to do certain actions like "Target.Interior.Color = vbYellow" when any value is entered into certain excel cells.

I have a KeyPressApi Class StartKeyPressLoop() that monitors the Message loop for keypress messages and the does:

  MsgBox ("raise event")
  RaiseEvent KeyPressed(msgMessage.wParam, iKeyCode, Selection, bCancel)

That all works fine.

The Workbook_Open does a Worksheets("Sheet1").Activate call that does:

    If CKeyWatcher Is Nothing Then
        Set CKeyWatcher = New KeyPressApi
    End If
    CKeyWatcher.StartKeyPressLoop

to begin the message monitoring.

I also have:

     Public Event KeyPressed _
    (ByVal KeyAscii As Integer, _
     ByVal KeyCode As Integer, _
     ByVal Target As Range, _
     ByRef Cancel As Boolean)

And in the worksheet:

Private Sub CKeyWatcher_KeyPressed(ByVal KeyAscii As Integer, ByVal KeyCode As Integer, ByVal Target As Range, Cancel As Boolean)
    MsgBox ("key pressed")
    If Not Intersect(Target, Range("B10:B11")) Is Nothing Then 'KeyPressed in B10 or B11
        valueEntered = True
        MsgBox ("Value entered")
        
    End If
End Sub

So the following should happen:

• Opening the Workbook should cause Worksheets("Sheet1").Activate to be executed.
• Worksheets("Sheet1").Activate should start CKeyWatcher.StartKeyPressLoop execution to monitor the message loop.
• On any key press StartKeyPressLoop does RaiseEvent KeyPressed(msgMessage.wParam, iKeyCode, Selection, bCancel).
• RaiseEvent should cause Private Sub CKeyWatcher_KeyPressed to run
• Private Sub CKeyWatcher_KeyPressed routine will set flags that tell me that a value in the cells that I am interested in have changed.
• The Private Sub Worksheet_Change() routine will then use these flags to do Target.Interior.Color = vbYellow

Based on the Msgboxes, the Worksheet_Activate is called, the RaiseEvent is executed for every key press, but the CKeyWatcher_KeyPressed is not invoked by the RaiseEvent, most of the time.

Why does the RaiseEvent not cause the CKeyWatcher_KeyPressed to be executed?

Is there an easier and more reliable way to do this without using a message loop monitor?

 

[Jaafar Tribak]

I have ran into problems with RaiseEvent when combined with APIs .. Have you tried making the CKeyWatcher_KeyPressed routine Public and executing it via the Call statement from inside the Class module instead of using RaiseEvent ? something like this:

Call Sheet1.KeyPressed(msgMessage.wParam, iKeyCode, Selection, bCancel)

Change "Sheet1" as needed

Is there an easier and more reliable way to do this without using a message loop monitor?

The only thing I can think of is using a Keyboard hook such as WH_KEYBOARD_LL .. I think it is better than using the Waitmessage/PeekMessage/GetMessage APIs inside a constant loop which has impact on performance and which can be easily interrupted accidentally (Actually, the RaiseEvent issue may be due to the loop) ... However, installing a hook is a bit dangerous and has to be set up properly to avoid potential crashings

Which excel version are you using ? and are you using a 32bit or 64bit Windows ?

 

[Jaafar Tribak]

You may also want to use a high resolution API Timer such as SetTimer/KillTimer together with the GetAsyncKeyState API which is easier to code than a propper keyboard hook but you won't be able to use the Cancel Argument of the custom event