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:
That all works fine.
The Workbook_Open does a Worksheets("Sheet1").Activate call that does:
to begin the message monitoring.
I also have:
And in the worksheet:
So the following should happen:
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?
I have a KeyPressApi Class StartKeyPressLoop() that monitors the Message loop for keypress messages and the does:
Code:
MsgBox ("raise event")
RaiseEvent KeyPressed(msgMessage.wParam, iKeyCode, Selection, bCancel)
The Workbook_Open does a Worksheets("Sheet1").Activate call that does:
Code:
If CKeyWatcher Is Nothing Then
Set CKeyWatcher = New KeyPressApi
End If
CKeyWatcher.StartKeyPressLoop
I also have:
Code:
Public Event KeyPressed _
(ByVal KeyAscii As Integer, _
ByVal KeyCode As Integer, _
ByVal Target As Range, _
ByRef Cancel As Boolean)
And in the worksheet:
Code:
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?