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 the properties 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

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.

Press ENTER to see the timestamp.

For the second timestamp and barcode, enter the barcode, use the following formula and press ENTER.

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

Blank cells were filled with random barcodes and timestamps.

Practice Section

Practice here.

Download Practice Workbook

Barcode Scanner Entry with Timestamp.xlsm

<< Go Back to Use Barcode Scanner | Barcode in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Larry Jul 24, 2024 at 2:51 AM

Hello, can you help me with this bar code and time stamp? Thank you

Shamima Sultana Jul 25, 2024 at 5:31 PM

Hello Larry,

Based on your previous comment.

To add the date to column K of the matched row of TCN of column B, you will need to use the Date function. It will provide the current date.

Here, I updated the VBA code to add the date. Make sure to place this code in the appropriate worksheet module where you are scanning the barcodes.

Private Sub Worksheet_Change(ByVal target As Range)
    If Not Intersect(target, Columns("M")) Is Nothing Then
        Z = Intersect(target, Columns("M")).Value
        If IsNumeric(Z) Then
            x = Application.Evaluate("MATCH(" & Z & ",B:B,0)")
        Else
            x = Application.Evaluate("MATCH(" & Chr(34) & Z & Chr(34) & ",B:B,0)")
        End If
        If Not IsError(x) Then
            Application.Goto Cells(x, 15)
            Cells(x, 11).Value = Date & " " & Time ' Adds the current date to column K
        End If
    End If
End Sub

Regards
ExcelDemy

Barcode Scanner Entry in an Excel Database with Timestamp – 3 Examples

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

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

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

Practice Section

Related Articles

Leave a reply Cancel reply