Using Excel OnKey without overriding the default behavior
By using Application.OnKey <key>, <procedure>
, I can get Excel to launch <procedure>
whenever is clicked <key>
.
I would like it to do what a push <key>
would do by default and then run <procedure>
. In other words, I don't want to use OnKey
to override what the key does, I want to use it to add to what the key does.
Background information:
I am using a barcode scanner that acts just like a keyboard, when it scans something, types in barcode characters, and then returns a carriage (char 13). This carriage return raises the Excel event OnKey "~"
.
Now I can scan the barcode and it puts the barcode value in the cell and then moves to the next cell. This is a good start. I want to have Excel run some code along with this so that it will put the value into a cell, move around the cell and execute the procedure.
source to share
Why not just flip the carriage return with SendKeys
, and let Excel handle it as it sees fit? This is admittedly a kludge, but perhaps nothing more than the rest of your code as you describe it! This would be the simplest and most readable solution - if it works.
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 place PlainOldCarriageReturn
at the beginning of the procedure called OnKey
.
My cursory testing shows it works, but you'll want to test it yourself.
source to share
Typically, you should simulate the action in your own code. If you want OnKey on Enter you can use code like this
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 the Worksheet event. If the barcode output is predictable, you can use the Worksheet_SelectionChange event. For example, if your barcode always outputs four numbers, a dash and two numbers, you can
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
This assumes your MoveAfterReturnDirection is xlDown, but you can code for other events. In fact, if the output is predictable, you can simply use the Worksheet_Change event to identify the barcode input and execute the procedure.
source to share