CODEWITHSUNDEEP
excelvba
Joe
Joe

Reputation: 3834

using Excel OnKey without overriding default key behavior

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.

Upvotes: 3

Views: 6128

Answers (2)

Jean-Fran&#231;ois Corbett
Jean-Fran&#231;ois Corbett

Reputation: 38510

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. 

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 PlainOldCarriageReturn at the beginning of the procedure called by OnKey.

My cursory testing indicates that this does works, but you'll want to test it yourself.

Upvotes: 0

Dick Kusleika
Dick Kusleika

Reputation: 33145

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.

Upvotes: 3

Related Questions