Barcode Scanner Macro

[onderon]

Hi,

I would like to ask for a help with a macro for Excel. I would like to use a Barcode scanner on an already existing list. It is an item inventory. All of the items have a barcode sticker on it, matching the number entry within the list. When I scan the item I would like the macro to jump to the cell where the matching number is then highlight the cell (or the whole row) to light green. Is it possible? The barcode scanner connects via USB.

Thanks!

 

[rollis13]

Have a try with what I came up with. The macro has to be pasted in the sheet's module since I'm using event Worksheet_Change to detect the scan input.
You will need to choose a cell where you can read the input from the barcode scanner. In the macro at the moment it's "A1", I suggest choosing a cell easy to select for the next scan input.
You will also have to indicate where the macro will serach the items of the inventory, at the moment it's in range "A2 and down".

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim BCode  As String
    Dim rng    As Range
    Dim srch   As Range
    If Not Target.Address = "$A$1" Then Exit Sub  'if it isn't the cell chosen for input... <-- change cell as needed
    BCode = Target.Value
    If BCode <> "" Then                           'if not empty...
        With Sheets("Sheet1")                     '<-- change sheet name as needed
            'search the BCode in the table
            Set rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp)) 'from A2 and down to last item <-- change range as needed
            Set srch = rng.Find(BCode, LookAt:=xlWhole) 'search in range
            With Application
                .EnableEvents = False             '
                .Undo                             'reset cell value for next input
                .EnableEvents = True              '
            End With
            rng.Interior.ColorIndex = xlNone      'clear old highlighting
            If srch Is Nothing Then               'if no BCode is found...
                MsgBox BCode & " isn't present in the list."
            Else
                Range(srch.Address).Select        'select found cell
                Range(srch.Address).Interior.ColorIndex = 35 'color light green
                Beep                              'emit a confirmation beep (not really necessary ;) )
            End If
            '.Range("A1").Select                   'get ready for next input
        End With
    End If
End Sub