vba 在不覆盖默认键行为的情况下使用 Excel OnKey
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9829332/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
using Excel OnKey without overriding default key behavior
提问by Joe
Using Application.OnKey <key>, <procedure>, I can cause Excel to run <procedure>whenever <key>is pressed.
使用Application.OnKey <key>, <procedure>，我可以<procedure>在<key>按下时使 Excel 运行。
What I'd like is for it to do whatever pressing <key>would have done by default, and thenrun <procedure>. In other words, I don't want to use OnKeyto override what a key does, I want to use it to addto what a key does.
我想要的是让它执行<key>默认情况下会执行的任何操作，然后运行<procedure>. 换句话说，我不想用来OnKey覆盖键的作用，我想用它来添加键的作用。
Background Info:
背景资料：
I'm using a barcode scanner that acts just like a keyboard when it scans something, typing in the characters of the barcode, followed by a carriage return (char 13). This carriage return does trigger Excel's OnKey "~"event.
我使用的条形码扫描仪在扫描某些东西时就像键盘一样，输入条形码的字符，然后回车（字符 13）。这个回车确实触发了 Excel 的OnKey "~"事件。
Right now, I can scan a barcode, and it puts the barcode's value in the cell, then moves down to the next cell. This is a good start. What I want to do is have Excel run some code along with this, so that it puts the value in the cell, moves down a cell, then runs a procedure.
现在，我可以扫描条形码，它会将条形码的值放入单元格中，然后向下移动到下一个单元格。这是一个好的开始。我想要做的是让 Excel 与此一起运行一些代码，以便它将值放入单元格中，向下移动一个单元格，然后运行一个过程。
采纳答案by Jean-Fran?ois Corbett
Why not just "rethrow" the carriage return using SendKeys, and let Excel deal with it as it sees fit? Admittedly, this is a kludge, but perhaps no more so than the rest of your code as you describe it! It would be the simplest and most readable solution -- if it works.
为什么不直接使用 来“重新抛出”回车SendKeys，并让 Excel 按照它认为合适的方式处理它？诚然，这是一个杂乱无章的事情，但也许并不比你描述的其他代码更是如此！这将是最简单和最易读的解决方案——如果它有效的话。
Sub PlainOldCarriageReturn()
Application.EnableEvents = False ' So it won't trigger OnKey.
Application.SendKeys "~", True ' Send a carriage return.
DoEvents ' Process the carriage return.
Application.EnableEvents = True ' Back to normal
End Sub
Just put PlainOldCarriageReturnat the beginning of the procedure called by OnKey.
只需放在PlainOldCarriageReturn由 调用的过程的开头即可OnKey。
My cursory testing indicates that this does works, but you'll want to test it yourself.
我的粗略测试表明这确实有效，但您需要自己进行测试。
回答by Dick Kusleika
Generally, you have to simulate the action in your own code. If you want to OnKey on Enter, you could use code like this
通常，您必须在自己的代码中模拟操作。如果你想 OnKey on Enter，你可以使用这样的代码
Sub SEtup()
Application.OnKey "~", "DoThis"
End Sub
Sub DoThis()
'Simulate enter key
If Application.MoveAfterReturn Then
Select Case Application.MoveAfterReturnDirection
Case xlDown
If ActiveCell.Row < ActiveCell.Parent.Rows.Count Then
ActiveCell.Offset(1, 0).Select
End If
Case xlToLeft
If ActiveCell.Column > 1 Then
ActiveCell.Offset(0, -1).Select
End If
Case xlToRight
If ActiveCell.Column < ActiveCell.Parent.Columns.Count Then
ActiveCell.Offset(0, 1).Select
End If
Case xlUp
If ActiveCell.Row > 1 Then
ActiveCell.Offset(-1, 0).Select
End If
End Select
End If
'This is your code
Debug.Print ActiveCell.Address
End Sub
Another option is to use a Worksheet event. If the barcode output is predictable, you might be able to use a Worksheet_SelectionChange event. For instance, if your barcode always outputs four numbers, and dash, and two numbers, you could
另一种选择是使用工作表事件。如果条形码输出是可预测的，您或许可以使用 Worksheet_SelectionChange 事件。例如，如果您的条形码总是输出四个数字、破折号和两个数字，您可以
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Offset(-1, 0).Value Like "####-##" Then
'do stuff here
Debug.Print Target.Address
End If
End Sub
That assumes your MoveAfterReturnDirection is xlDown, but you could code for other eventualities. In fact, if the output is predictable, you might just use the Worksheet_Change event to identify barcode input and run a procedure.
假设您的 MoveAfterReturnDirection 是 xlDown，但您可以为其他可能性编码。事实上，如果输出是可预测的，您可能只使用 Worksheet_Change 事件来识别条形码输入并运行一个过程。
