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

2 answers

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.



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.



All Articles