using Excel OnKey without overriding default key behavior

Question

Using Application.OnKey <key>, <procedure>, I can cause Excel to run <procedure> whenever <key> is pressed.

What I'd like is for it to do whatever pressing <key> would have done by default, and then run <procedure>. In other words, I don't want to use OnKey to override what a key does, I want to use it to add to what a key does.

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.

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.

Answer 1

Generally, you have to simulate the action in your own code. If you want to OnKey on Enter, you could 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 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

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.