How to Create a Barcode Scanner to Track Check-in & Check-out Times in Excel (Step-by-Step)

Step 1: Insert a Command Button in Excel

Define a cell where you will put the input barcode. Here, I will put it in cell C4.
Create a table where you want the output. Here, I created a table that contains the Barcode, Check-in, and Check-out times.

Go to the Developer tab.
Select Insert.
Select the Command Button from ActiveX Controls.

Click and drag your mouse cursor where you want the Command Button.

You will see that you have inserted a Command Button.

To change the properties of the Command Button, Right-Click on it.
Select Properties.

The Properties dialog box will appear.
Change the caption as you want. Here, I changed mine to Scan.

To change the font, select Font.
Click on the marked button in the following picture.

The Font dialog box will appear.
You can change the Font, Font Style, and Size as you like. I selected Bold as the Font Style and 14 as the Size.
Select OK.

In the following picture, you can see how my Command Button looks at this point.

Step 2: Write VBA Code

Right-click on the Command Button.
Select View Code.

A module will open with a Private Sub Procedure.
Enter the following code in that module:

Private Sub CommandButton1_Click()
Dim bar_code As String
Dim barcode_range As Range
Dim row_no As Long
bar_code = ActiveSheet.Cells(4, 3)
    Set barcode_range = ActiveSheet.Range("B8:D100").Columns(1).Find(What:=bar_code, _
    LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, _
    SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
    If barcode_range Is Nothing Then
        ActiveSheet.Range("B8:D100").Columns(1).Find("").Select
        ActiveCell.Value = bar_code
        ActiveCell.Offset(0, 1).Select
        ActiveCell.Value = Date & "  " & Time
        ActiveCell.NumberFormat = "m/d/yyyy h:mm AM/PM"
        ActiveSheet.Cells(4, 3) = ""
    Else
        row_no = barcode_range.Row
        ActiveSheet.Cells(row_no, 2).Select
        ActiveCell.Offset(0, 2).Select
        ActiveCell.Value = Date & "  " & Time
        ActiveCell.NumberFormat = "m/d/yyyy h:mm AM/PM"
        ActiveSheet.Cells(4, 3) = ""
    End If
End Sub

How Does the Code Work?

Here, a Private Sub Procedure was already created by the Command Button named CommandButton1_Click.
Then, I declared a variable named bar_code as String, a variable named barcode_range as Range, and another variable named row_no as Long.
Next, I defined the bar_code by using the ActiveSheet.Cells property. Here, Cells(4, 3) mean cell C4.
After that, I used the Set Statement to define the barcode_range. Then, I used the Find method to find the bar_code in the barcode_range.
Further, I used the If Statement to give 2 different situations. If the bar_code does not match any value in the barcode_range, then it will return the barcode and the check-in time in the cell beside the barcode. Otherwise, it will return the checkout time.
Then, I ended the If Statement.
Finally, I ended the Sub Procedure.

Lastly, Save the code and go back to your worksheet.

Step 3: Run Macros

Type the barcode in the input cell.

Click on the Command Button.

You will see that the barcode and the time you scanned it are entered in the output table as Check-in.

When the product is going out of the warehouse, enter the barcode in the input cell again.
Select Scan.

You will see that the Checkout time is entered in the output table.

Final Output

In the following dataset, I tracked check-in and check-out times for 5 products using the Excel barcode scanner. It gives accurate results with a single click.

Things to Remember

It should be noted that if you are working with VBA, you must save the Excel file as an Excel Macro-Enabled Workbook. Otherwise, the VBA code won’t work.

Practice Section

A practice sheet for creating a barcode scanner to track check-in and check-out time in Excel is here.

Download the Practice Workbook

You can download the practice workbook from here.

Creating Barcode Scanner to Track Check in & out.xlsm

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

Get FREE Advanced Excel Exercises with Solutions!

Sub DuplicateCheckInAndOut() Dim ws As Worksheet Dim lastRow As Long Dim searchValue As Variant Dim cellB As Range Dim foundCell As Range Dim loopingRange As Range Dim foundInMiddle As Boolean Set ws = ThisWorkbook.Sheets("Sheet1") lastRow = ws.Cells(ws.Rows.Count, "B").End(xlUp).Row searchValue = ws.Range("C4").Value If lastRow < 9 Then lastRow = 9 Set loopingRange = ws.Range("B9:B" & lastRow) Set foundCell = loopingRange.Find(What:=searchValue, LookIn:=xlValues, LookAt:=xlWhole) If foundCell Is Nothing Then If lastRow = 9 Then ws.Cells(lastRow, "B").Value = searchValue ws.Cells(lastRow, "C").Value = Date & " " & Time ws.Cells(lastRow, "C").NumberFormat = "m/d/yyyy h:mm AM/PM" Else ws.Cells(lastRow + 1, "B").Value = searchValue ws.Cells(lastRow + 1, "C").Value = Date & " " & Time ws.Cells(lastRow + 1, "C").NumberFormat = "m/d/yyyy h:mm AM/PM" End If Exit Sub End If For Each cellB In loopingRange If cellB.Value = searchValue Then If IsEmpty(cellB.Offset(0, 2).Value) Then cellB.Offset(0, 2).Value = Date & " " & Time cellB.Offset(0, 2).NumberFormat = "m/d/yyyy h:mm AM/PM" foundInMiddle = False Else If cellB.Row = lastRow Then ws.Cells(lastRow + 1, "B").Value = searchValue ws.Cells(lastRow + 1, "C").Value = Date & " " & Time ws.Cells(lastRow + 1, "C").NumberFormat = "m/d/yyyy h:mm AM/PM" Else foundInMiddle = True End If End If End If Next cellB If foundInMiddle = True Then ws.Cells(lastRow + 1, "B").Value = searchValue ws.Cells(lastRow + 1, "C").Value = Date & " " & Time ws.Cells(lastRow + 1, "C").NumberFormat = "m/d/yyyy h:mm AM/PM" End If End Sub

2 Comments

NC Jan 5, 2024 at 12:46 AM

Works great but I want to use it to track equipment that is under calibration control. I can’t get it recognize a duplicate entry and create another entry/timestamp after it was checked in/out. it just keeps updating the last cell. can you help?

Lutfor Rahman Shimanto Jan 15, 2024 at 10:48 AM

Hello NC

Thanks for sharing your requirements. You wanted a setup not to overwrite the existing entry but to create a new entry timestamp when an item is checked in or out more than once.

I am delighted to inform you that I have developed an Excel VBA Sub-procedure to fulfil your requirements. So, follow these steps:

1. Press Alt+F11 to the VBA Editor window.

2. Click on Insert followed by Module.

3. Insert the following code in the module and Run.

After inserting the bar code and running the sub-procedure, you will see an output like the following GIF.

Hopefully, the idea will help you to reach your goal. I have attached the solution workbook. Good luck.

DOWNLOAD SOLUTION WORKBOOK

Regards
Lutfor Rahman Shimanto
Excel & VBA Developer
ExcelDemy