How to create an Auto Enter Barcode Scanner in Excel

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 – Open 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 – Use the VBA 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.