可以在Excel VBA中捕获并模拟KeyDown事件吗? [英] Can I capture and emulate a KeyDown event in Excel VBA?
问题描述
Arun Singh对类似的问题给了很好的答案(编辑单元格时按键时是否有任何事件发生?如果用户使用箭头键滚动,我想设置一个标志来防止Selection_Change事件的执行。
Arun Singh gave a great answer to a similar question (Is there any event that fires when keys are pressed when editing a cell?). I want to set a flag to prevent execution of Selection_Change event if the user is scrolling with the arrow keys.
推荐答案
很容易其实。我正在为 UP 和 DOWN 箭头键进行演示。您可以添加更多内容,如 RIGHT / LEFT / TAB / ENTER 等...我已经评论了可以添加密钥的部分。
It's pretty easy actually. I am demostrating it for UP and DOWN arrow key. You may add more to it like RIGHT/LEFT/TAB/ENTER etc... I have commented the part where you can add the keys.
将其粘贴到工作表代码区域中
Paste this in the worksheet code area
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If CancSelEvnt = False Then
'
'~~> Rest of the code for the Selection Change
'
Else
'~~> Only for demostration purpose. Remove Msgbox later
MsgBox "User pressed one of the navigation keys"
CancSelEvnt = False
End If
End Sub
将其粘贴到模块中
Option Explicit
'~~> We need this as this will help us in cancelling the
'~~> Selection chnage event
Public CancSelEvnt As Boolean
Private Type POINTAPI
x As Long
y As Long
End Type
Private Type MSG
hwnd As Long
Message As Long
wParam As Long
lParam As Long
time As Long
pt As POINTAPI
End Type
Private Declare Function WaitMessage Lib "user32" () As Long
Private Declare Function PeekMessage Lib "user32" Alias "PeekMessageA" _
(ByRef lpMsg As MSG, ByVal hwnd As Long, _
ByVal wMsgFilterMin As Long, _
ByVal wMsgFilterMax As Long, _
ByVal wRemoveMsg As Long) As Long
Private Declare Function TranslateMessage Lib "user32" _
(ByRef lpMsg As MSG) As Long
Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" _
(ByVal hwnd As Long, _
ByVal wMsg As Long, _
ByVal wParam As Long, _
lParam As Any) As Long
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Const WM_KEYDOWN As Long = &H100
Private Const PM_REMOVE As Long = &H1
Private Const WM_CHAR As Long = &H102
Private bExitLoop As Boolean
Sub StartKeyWatch()
Dim msgMessage As MSG
Dim bCancel As Boolean
Dim iKeyCode As Integer
Dim lXLhwnd As Long
On Error GoTo errHandler:
Application.EnableCancelKey = xlErrorHandler
bExitLoop = False
lXLhwnd = FindWindow("XLMAIN", Application.Caption)
Do
WaitMessage
If PeekMessage _
(msgMessage, lXLhwnd, WM_KEYDOWN, WM_KEYDOWN, PM_REMOVE) Then
iKeyCode = msgMessage.wParam
TranslateMessage msgMessage
PeekMessage msgMessage, lXLhwnd, WM_CHAR, _
WM_CHAR, PM_REMOVE
If iKeyCode = vbKeyBack Then SendKeys "{BS}"
If iKeyCode = vbKeyReturn Then SendKeys "{ENTER}"
bCancel = False
'
'~~> This is the main part where we check what key is pressed
'
If iKeyCode = vbKeyDown Then '<~~ Down
SendKeys "{DOWN}"
CancSelEvnt = True
ElseIf iKeyCode = vbKeyUp Then '<~~ UP
SendKeys "{UP}"
CancSelEvnt = True
'
'~~> And so on for the rest of the navigation keys
'
Else
CancSelEvnt = False
End If
If bCancel = False Then
PostMessage _
lXLhwnd, msgMessage.Message, msgMessage.wParam, 0
End If
End If
errHandler:
DoEvents
Loop Until bExitLoop
End Sub
Sub StopKeyWatch()
bExitLoop = True
End Sub
这里是您可能需要的东西;)
And here is something that you may need ;)
VBKey代码列表
vbKeyLButton Left Mouse Button
vbKeyRButton Right Mouse Button
vnKeyCancel Cancel Key
vbKeyMButton Middle Mouse button
vbKeyBack Back Space Key
vbKeyTab Tab Key
vbKeyClear Clear Key
vbKeyReturn Enter Key
vbKeyShift Shift Key
vbKeyControl Ctrl Key
vbKeyMenu Menu Key
vbKeyPause Pause Key
vbKeyCapital Caps Lock Key
vbKeyEscape Escape Key
vbKeySpace Spacebar Key
vbKeyPageUp Page Up Key
vbKeyPageDown Page Down Key
vbKeyEnd End Key
vbKeyHome Home Key
vbKeyLeft Left Arrow Key
vbKeyUp Up Arrow Key
vbKeyRight Right Arrow Key
vbKeyDown Down Arrow Key
vbKeySelect Select Key
vbKeyPrint Print Screen Key
vbKeyExecute Execute Key
vbKeySnapshot Snapshot Key
vbKeyInsert Insert Key
vbKeyDelete Delete Key
vbKeyHelp Help Key
vbKeyNumlock Delete Key
vbKeyA through vbKeyZ are the key code constants for the alphabet
vbKey0 through vbKey9 are the key code constants for numbers
vbKeyF1 through vbKeyF16 are the key code constants for the function keys
vbKeyNumpad0 through vbKeyNumpad9 are the key code constants for the numeric key pad
Math signs are:
vbKeyMultiply - Multiplication Sign (*)
vbKeyAdd - Addition Sign (+)
vbKeySubtract - Minus Sign (-)
vbKeyDecimal - Decimal Point (.)
vbKeyDivide - Division sign (/)
vbKeySeparator - Enter (keypad) sign
请这是 msdn链接的密钥代码。
And Of course THIS msdn link for the key codes.
这篇关于可以在Excel VBA中捕获并模拟KeyDown事件吗?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!