Barcode Scanner utilities for Excel - Advice sought

[evilC1]

Hi all, first time poster here.
I am looking into solutions for making a USB barcode scanner (ie "keyboard wedge" type that emulates the keyboard) more useful in conjunction with Excel, via VBA / Userforms etc.
I have some current solutions involving AutoHotkey, but am looking into what VBA etc can offer me.
I am, however, pretty much new to VBA...

The kind of features that I am looking to implement are:

• Spreadsheet protection
  Stop the barcode scanner from overwriting data in Excel (ie user pulls trigger while cursor is in cell), whilst still allowing editing of the spreadsheet via the keyboard.
  Likely to be handled by trapping a "preamble" character that the scanner is configured to send before typing whatever it scanned.
• Quick Searching
  Allow the user to see if the scanned item is present in the spreadsheet.
  Audible feedback would be nice.
• Audit Mode
  Keeps some record of which items present in the spreadsheet have been found.
  Maybe with "Found x of y" counter.
  Option in userform to select a column on spreadsheet to mark when found (Change colour, put Y in cell etc)
• Stock Take Mode
  For compiling a list of what is where. Barcodes are created with a prefix (eg !StockRoom01) and when one of those is scanned, scanning subsequent barcodes will add the scanned item to the StockRoom01 worksheet.

I have managed to knock up some proof-of-concept code (Largely cobbled together from stuff on this site). The "preamble character" in this case is Tilde (~) and most of the code is concerned with giving the TextBox TextScannerInput focus whenever the tilde character is pressed.

UserForm1:

Option Explicit 
Dim TextBoxes() As New CControls
Dim TextBoxCount As Long


Dim OptionButtons() As New CControls
Dim OptionButtonCount As Long
 
' Add Listener for Tilde (~) Key to all Controls


Private Sub UserForm_Initialize()
    Dim ctl As MSForms.Control


    TextBoxCount = 1
    OptionButtonCount = 1
    
    For Each ctl In Me.Controls
        Select Case TypeName(ctl)
            Case "TextBox"
                ReDim Preserve TextBoxes(1 To TextBoxCount)
                Set TextBoxes(TextBoxCount).TextGroup = ctl
                TextBoxCount = TextBoxCount + 1
            Case "OptionButton"
                ReDim Preserve OptionButtons(1 To OptionButtonCount)
                Set OptionButtons(OptionButtonCount).OptionGroup = ctl
                OptionButtonCount = OptionButtonCount + 1
        End Select


    Next ctl


End Sub


' Detect Enter key pressed in TextScannerInput
Private Sub TextScannerInput_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  If KeyCode = vbKeyReturn Then
    'MsgBox "It's your thang..." & vbCrLf & "Do whatcha wanna do...", 64, "Enter key was pressed."
    KeyCode = 0
  End If
End Sub

Class Module CControls:

Option Explicit
 
Public WithEvents TextGroup As MSForms.TextBox
Public WithEvents OptionGroup As MSForms.OptionButton
 
Private Sub TextGroup_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = PreambleCheck(KeyAscii)
End Sub


Private Sub OptionGroup_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = PreambleCheck(KeyAscii)
End Sub


Private Function PreambleCheck(KeyAscii) As Integer
    ' Check for preamble character from barcode scanner
    If KeyAscii = 126 Then
        PreambleCheck = 0
        UserForm1.TextScannerInput.Value = ""
        UserForm1.TextScannerInput.SetFocus
    Else
        PreambleCheck = KeyAscii
    End If
End Function

Regarding this code:
Is there not a better way to add the _KeyPress listener to all controls rather than having to make arrays for each type of control?
Failing that, is there at least a way to handle pushing to the arrays in UserForm_Initialize better? This ReDim business seems like a pain in the rear end, I tried UBounds() to avoid having to keep individual counters, but couldn't get it to work.

Anyway, I suppose the main reason I am posting is to seek advice on the best way in general of approaching this project.

Some thoughts:

1. How would one make the code portable such that it can be added to any spreadsheet?
   Ideally by semi-technical users.
   ie what is the best way to allow people to add the userform and all associated logic to any spreadsheet they wish?
2. Is there a way to trap the preamble character (eg ~) whilst the focus is a cell in the spreadsheet, rather than the userform?
   ie, if user has a cell selected when he pulls the scanner trigger, set the focus to the userform input box without altering the spreadsheet.
   Solution should also not make point (1) more complicated if possible.
   I could probably solve this with AHK, but an all-excel solution would be nice.

Any thoughts appreciated. TIA

 

[brucef2112]

Sometime simple beats technical problems down. You may want to back off the tech side of scanner codes preamble and stuff and go for some basic differences between scanners and humans. A small chunk of code will handle this based on the fact that a barcode scanner can 'key' faster than a human.
An 11 digit barcode is scanned in less than 1 second (I think 'blink of an eye' is the technical term).
The same 11 digits by a human would be about 2-3 seconds if an Olympic sport.
so the sudo code for an Onkey event.
wait for a key entry.
start a timer.
wait for Enter key then stop timer.
look at elapsed time from first key to Enter key.
(barcode scanners can be set to send an Enter key after reading barcode)
If less than 1 second it must have come from a barcode scanner. Put back the original data. otherwise assume it was keyed by a human, let them enter away.

Check the net for On Key Events Timer events in VBA VB or even.....QBasic. There a billions of lines of code in qbasic out there. I'm sure something can be found and modified to handle this simple event in Excel.

good luck.
bruce

 

[brucef2112]

OK I found this example provide by dlmille and seems to be a great frame work to start from.
http://www.experts-exchange.com/Sof...Office_Suites/MS_Office/Excel/Q_27118905.html

Capturing keypress in Excel Worksheet with VBA
About half way down in the replies at the 'Verified Answer", is the sample sheet hookKeyboard-r2.xlsm
to capture key strokes.
It is well document with IFs and CASEs to stick you specific code needs.
There is a GoTo statement, when removed will allow you to control what happens for your situation.

When first opened, it's not real clear, but you have to click on the big honking grey instructions 'button' to start the key hook.
And it does make it clear that entering 'z' stops the key hook.
When running, the results of you key press will show on the status bar at the lower right of Excel.

Note also that with the hook running you can't step through code into the VB editor using the 'keys' (F8) or any other key press. So If you want to step through the code you have to use your mouse and the Debug menu to do it.
BE SURE TO RUN THE UNHOOK SUB (with mouse) to disable the hook so you can do stuff.

Also check out his previous post, it has a link with good info; 'Managing Lowlevel keyboard hooks'.