How to create an Auto Enter Barcode Scanner in Excel

What Is a Barcode?

Barcode is a machine-readable code. It encodes information with spaces and varying widths of parallel lines. These spaces and lines represent numeric values and characters.

Step 1 – Prepare the Worksheet for Data Entry

Create a data table to place the Barcode Numbers (B5:B14) and to automatically enter Date & Time (C5:C14).

Step 2 – Setup the Visual Basic Window

Go to the Developer tab and select Visual Basic.

In the Visual Basic window, double-click Sheet1.

Note: The code is entered in the specific sheet. If you type the code in the Module, it will run the whole workbook.

Select Worksheet as the object sheet.

Select Change as the VBA event.

Step 3 – Insert the VBA Macro Code

Enter this VBA Macro code in the blank sheet.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim TableRange As Range
Dim DateTimeRange As Range

Set TableRange = Range("B5:B14")

If Intersect(Target, TableRange) Is Nothing Then Exit Sub

Set DateTimeRange = Range("C" & Target.Row)

If DateTimeRange.Value = "" Then
   DateTimeRange.Value = Now

End If

End Sub

Range (“B5:B14”) and Range (“C”) are changeable according to your dataset. If your Barcode Numbers are in A4:A10 and outputs in column B, the code will be Range (“A4:A10”) and Range (“B”) respectively.