Using a barcode scanner to search and edit spreadsheet with Visual Basic for Applications

Question

Trying to use a barcode scanner to automatically search a spreadsheet and check it off (routine verifications of assets).

I recorded the macro and it searches a specific string then changes the cell color to "check it off".

How can I make it wait for input from the scanner then search that input instead? I want the "IT2000" to be a variable that changes based on the barcode scanner.

Recored Macro:

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+b
'
    Sheets("S15-137").Select
    Cells.Find(What:="IT2000", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Selection.Style = "Good"
End Sub

tried this and the scanner input works now but the code is failing. Also, how do I make it search all sheets by default?

Sub Macro1()
'
' Macro1 Macro
'
' Keyboard Shortcut: Ctrl+b
'

Dim scannerInput As String
Dim cellFound As Range

scannerInput = Application.InputBox("Scan barcode")

Set cellFound = ThisWorkbook.Sheets("S15-137").Cells.Find(What:=Barcode, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False)
    If Not cellFound Is Nothing Then
        cellFound.Style = "Good"
    Else
        MsgBox "Value not found"
    End If

End Sub

As far as I know Excel does not support barcode scanners out of the box. So you will have to write that code and probably you need a third party library from your specific barcode scanner. You need to ask that the manufacturer of your barcode scanner. Nobody here can tell you that. • You can replace your find string `Cells.Find(What:=YourCodeVariable` then. But how to get the barcode from the scanner into your variable is something we cannot solve here. — Pᴇʜ, Feb 10 '20 at 13:51
You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) especially when you use the macro recorder your code should be refined afterwards. — Pᴇʜ, Feb 10 '20 at 13:53

Kirill Tkachenko · Answer 1 · 2020-02-10T14:08:25.263

As far as I know, you can make a barcode scanner insert the value it has just scanned into Excel sheet. If you can do that, then you can write a Worksheet_Change event. Add an empty sheet, where the scanner will output the barcodes, and in its sheet module insert the following code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Barcode As String
    Dim FindCell As Range
    Barcode = Target.Value
    Set FindCell = ThisWorkbook.Sheets("S15-137").Cells.Find(What:=Barcode, LookIn:=xlFormulas, LookAt _
            :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
            False, SearchFormat:=False)
    If Not FindCell Is Nothing Then
        FindCell.Style = "Good"
    End If
End Sub

Using a barcode scanner to search and edit spreadsheet with Visual Basic for Applications

1 Answers1