Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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.


Download Practice Workbook

You can download the practice workbook from here.


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.


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

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.

Insert Command Button in Excel for Barcode Scanner to Track Chcek in and Check Out

  • 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.

Dragging Mouse Cursor to Create Barcode Scanner for Check in and Check Out in Excel

  • 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.

Changing Properties of Command Button to Create Barcode Scanner to Track Check in and Check out in Excel

  • 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.

Read More: How to Add Barcode Font in Excel (Installation and Application)


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.

Write VBA Code for Barcode Scanner to Track Check in and Check out

  • 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.

Read More: How to Use Barcode Scanner for Inventory in Excel (with Easy Steps)


Similar Readings


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.

Run Macros for Barcode Scanner to Track Check in and Check out in Excel

  • 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.

Read More: Creating a Barcode Scanner Macro in Excel (with Easy Steps)


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.

Final Output for Barcode Scanner to Track Check in and Check out in Excel

Read More: How to Create Barcode Without Font in Excel (2 Smart Methods)


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.

Practice Sheet for Barcode Scanner to Track Check in and Check out in Excel


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.


Related Articles

Mashhura Jahan

Mashhura Jahan

Hey! Welcome to my profile. Right now, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo