Barcode Scanner Entry in an Excel Database with Timestamp

There are barcodes with corresponding timestamps in the dataset below.

Example 1- Apply Barcode Scanner Entries to an Excel Database with Start and End Time

Steps:

Go to Developer >> Visual Basic.

In the VBA editor window: Select Insert >> Module.

Enter the following code in the VBA

Sub BarcodeTimestamp()
Dim Bar_Code As String
Dim Barcode_Range As Range
Dim Row_Number As Long
Bar_Code = ActiveSheet.Cells(4, 3)
If Bar_Code <> "" Then
Set Barcode_Range = ActiveSheet.Range("B5:B100").Find(What:=Bar_Code, _
LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
If Barcode_Range Is Nothing Then
ActiveSheet.Range("B5:B100").Find("").Select
ActiveCell.Value = Bar_Code
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Date & "  " & Time
ActiveCell.NumberFormat = "dd/mm/yyyy h:mm:ss AM/PM"
ActiveSheet.Cells(4, 3) = ""
Else
Row_Number = Barcode_Range.Row
ActiveSheet.Range(Cells(Row_Number, 2), Cells(Row_Number, 10)).Find("").Select
ActiveCell.Value = Date & "  " & Time
ActiveCell.NumberFormat = "dd/mm/yyyy h:mm:ss AM/PM"
ActiveSheet.Cells(4, 3) = ""
End If
End If
ActiveSheet.Cells(4, 3).Select
End Sub

Necessary variables were declared: Bar_Code As String, Barcode_Range As Range and Row_Number As Long. The position of the Bar_code was set in C4 (ActiveSheet.Cells(4,3)) and the ActiveSheet.Range, ActiveCell.Value properties of VBA were used. The Time Format was set to “dd/mm/yyyy h:mm:ss” to differentiate timestamps.

This is a preview of the dataset structure.

Open the Worksheet to enter the barcode scanner id in the VBA Project and enter the following code.

Private Sub Worksheet_Change(ByVal target_value As Range)
If Not Intersect(target_value, Me.Range("C4")) Is Nothing Then
Call BarcodeTimestamp
Application.EnableEvents = True
End If
End Sub

The BarcodeTimestamp Macro was called and the Application Events were enabled by this Private Sub Procedure.

Go back to your sheet and run the Macro.

Enter the barcode number.

Press the ENTER and you will see the Product ID with the timestamp of Start Time (when the product was brought to the store).

Suppose you sold this product. Copy the Barcode number and you will see the End Time in the corresponding cell.

To update the timestamp, you can either delete the previous timestamp and apply for a new entry or copy the barcode to new cells to keep updated timestamps.

In the following picture, blank cells were filled with random barcodes and previous and updated timestamps.

Example 2 – Barcode Scanner Entry with Timestamp of Start Time Only

Steps:

Follow the procedure of Example 1 to open the VBA Editor.
Open the Worksheet to enter the barcode scanner id in the VBA Project and enter the following code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim x As Integer
For x = 5 To 200
If Cells(x, 2).Value <> "" And Cells(x, 3).Value = "" Then
Cells(x, 3).Value = Date & " " & Time
Cells(x, 3).NumberFormat = "dd/mm/yyyy h:mm:ss AM/PM"
End If
Next
End Sub

x is declared As Integer, a VBA If Statement and properties like Cells.Value and Cells.NumberFormat are used to create the timestamp.

Go back to your sheet and enter a barcode.

Press the ENTER to see the timestamp.

In the following picture, blank cells were filled with random barcodes and timestamps.

Example 3 – Applying an Excel Formula to Enter a Barcode in a Database with Timestamp

Use the IF and the NOW functions.

Steps:

Set your Calculation Option to Manual as the NOW function is a dynamic function, which provides the current time.

Enter a barcode in B5 and use the following formula in C5.

=IF(B5<>"",NOW(),"")

The IF function operates a logical test to see whether B5 is empty. If it’s not empty, the formula returns the current time with the help of the NOW function. Otherwise, it returns blank.