Mashhura Jahan
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.
- 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.
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.
- 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
- [Solved] Barcode Scanner Not Going to Next Line in Excel
- Barcode Scanner Entry to Excel Database with Timestamp (3 Basic Cases)
- How to Generate Code 128 Barcode Font for Excel (With Easy Steps)
- Use Code 39 Barcode Font for Excel (with Easy Steps)
- How to Print Barcode Labels in Excel (with 4 Easy Steps)
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.
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.
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.
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
- How to Generate 2D Barcode in Excel (with Easy Steps)
- Convert Numbers to Barcode in Excel (3 Easy Ways)
- How to Move Barcode Scanner to Next Row in Excel (2 Ways)
- Use EAN 13 Barcode Generator in Excel (2 Easy Ways)
- How to Calculate Barcode Check Digit with Excel Formula
- Make Auto Enter with Barcode Scanner in Excel
- How to Create Barcode Using 3 of 9 Font in Excel
