Creating Barcode Scanner to Track Check in & Check out Time in Excel

You can track check in time and check out time by using a barcode. Barcode provides a unique code for a specific item. So, it becomes easy to track an item by using its barcode. The objective of this article is to explain how you can create a barcode scanner to track check in and check out time in Excel.

What Is Barcode?

A barcode is a code that is machine-readable and it provides quick identification of an item. Barcodes consist of numbers and parallel lines with different widths. They are used in warehouses to track inventory, check in time or check out time of a product.

What Is Barcode Scanner?

A barcode scanner is used to scan a barcode and give information about the product. The Barcode scanner takes the barcode as input and gives the information about the barcodes as output. Generally, the barcode scanner uses a light source to read the barcode visually.

Creating Barcode Scanner to Track Check in & Check out Time in Excel: Step by Step Procedures

In this article, I will explain how you can create a barcode scanner in Excel to track check in and check out times. Here, you will have to put the barcode number as input and the scanner will provide the check in and check out time against it as output. You will be able to track when a product came into the warehouse and when it left by using the scanner. Let’s see the steps.

Step-01: Insert Command Button in Excel

In this first step, I will insert the command button. Later, I will use this command button to scan the barcodes.

In the beginning, define a cell where you will put the input barcode. Here, I will put the input barcode in cell C4.
Then, create a table where you want the output. Here, I created a table that contains the Barcode, Check in, and Check out times.

Afterward, go to the Developer tab.
Then, select Insert.
Next, select Command Button from ActiveX Controls.

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

Finally, you will see that you have inserted a Command Button.

Next, to change the properties of the Command Button, Right-Click on it.
Then, select Properties.

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

Next, to change the font select Font.
Then, Click on the marked button in the following picture.

Further, the Font dialog box will appear.
Then, change the Font, Font Style, and Size as you want. Here, I selected Bold as the Font Style and 14 as the Size.
Lastly, select OK.

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

Step-02: Write VBA Code

Here, I will write the VBA code for the Command Button. So that it scans the barcode and returns the check in time or the check out time when you click on it.

Firstly, Right-Click on the Command Button.
Secondly, select View Code.

Afterward, a module will open with a Private Sub Procedure.
Write 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 check out time.
Then, I ended the If Statement.
Finally, I ended the Sub Procedure.

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

Step-03: Run Macros

In this step, I will show you how you can run the macros and get a barcode scanner to track check in and check out times in Excel.

To begin with, write the barcode in the input cell.

Then, Click on the Command Button.

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

Next, when the product is going out of the warehouse, enter the barcode in the input cell again.
Then, select Scan.

Finally, you will see that the Check out time is entered in the output table.

Final Output

In the following picture, you can see the final output. Here, I tracked check in and check out times for 5 products by using the barcode scanner in Excel. It gives accurate results with a single click.

Things to Remember

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

Practice Section

Here, I have provided a practice sheet for you to practice creating a barcode scanner to track check in and check out time in Excel.

Download Practice Workbook

You can download the practice workbook from here.

Creating Barcode Scanner to Track Check in & out.xlsm

Conclusion

To conclude, I tried to cover how you can create a barcode scanner to track check in and check out time in Excel. Here, I explained it with 3 easy steps. I hope it was clear to you. Lastly, feel free to comment in the comment section if you face any problems.

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

Get FREE Advanced Excel Exercises with Solutions!

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.


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

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