Barcode Scanner Macro

onderon

New Member
Joined
Mar 24, 2022
Messages
1
Office Version
  1. 2021
Platform
  1. Windows
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!
 

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
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".
VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,223,701
Messages
6,173,929
Members
452,538
Latest member
deeme

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top