Certainly, Excel’s ability to store barcodes in a cell is a nifty feature. That said, however, the world we live in is far from perfect! At times, the barcode scanner may not automatically move to the next line. Keeping this in mind, in this article we’ll see in detail how to fix barcode scanner not going to next line in Excel. In addition, we’ll also learn how to insert barcodes in Excel.
Download Practice Workbook
You can download the practice workbook from the link below.
Why Barcode Scanner Is Skipping Lines in Excel
In a nutshell, the barcode engine skips to the next line when it encounters Carriage Return (CR), Line Feed (LF), and TAB characters when scanning a barcode. In this case, adding these extra characters by mistake can result in the skipping of lines in Excel.
Now, to resolve this issue, you can paste the scanning codes into a Microsoft Word file and click the Show/Hide button, in the Home tab to display any additional CR, LF, or TAB characters.
3 Solutions If Barcode Scanner Is Not Going to Next Line in Excel
Assuming the List of ID and Barcodes dataset shown in the B4:C13 cells which contain the ID number and the corresponding Barcode respectively. So, let’s have a glance at the solutions described below with simple and easy illustrations.
Here, we have used the Microsoft Excel 365 version, you may use any other version according to your convenience.
Solution-1: Using TAB and ENTER Keys
Let’s begin with the simplest way to move to the next line, that is to say, using the TAB and ENTER keys. So, let’s see it in action.
📌 Steps:
- Initially, go to the C5 cell and enter the expression given below.
="("&B5&")"
Here, the B5 cell refers to the ID which is 123ABC8462.
- Next, press the TAB key to move to the D5 cell.
- Then, type in “OK” to confirm the scanning >> hit the ENTER button to jump to the next line.
Now, the cursor moves to the next line in the C6 cell, ready to store the next barcode.
- Similarly, repeat the same procedure for the other cells to obtain the results shown in the image below.
Read More: How to Generate Barcode Numbers in Excel (with Easy Steps)
Solution-2: Automating the TAB and ENTER Keys with VBA Code
You might be wondering, is there a way to automate the previous process? Then VBA Code has you covered. So, just follow these steps.
📌 Steps:
Now, this opens the Visual Basic Editor in a new window.
- Second, Double-Click on the sheet name where you want to insert the VBA code.
For your ease of reference, you can copy the code from here and paste it into the window as shown below.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 2 Then
Target.Offset(0, 1).Select
ElseIf Target.Column = 3 Then
Target.Offset(1, -1).Select
End If
End Sub
⚡ Code Breakdown:
Here, I will explain the VBA code in the following steps:
- To start, use the If Else statement and the Offset property to move from column C to column D and select cell D5.
- Finally, use the Offset property to jump to the next line in the C6 cell.
- Third, navigate to the C5 cell and enter the formula given below.
="("&B5&")"
In the above expression, the B5 cell refers to the ID which is 123ABC8462.
- Now, pressing the ENTER key moves the cursor to the D5 cell.
- Afterward, type in OK and hit the ENTER key to move to the next line, i.e. the C6 cell.
Likewise, apply the same process and your output should look like the screenshot shown below.
Read More: How to Use Barcode Scanner in Excel (2 Suitable Ways)
Solution-3: Applying VBA code
Alternatively, you can also apply the following VBA code to move to the next line in Excel. Now, allow me to demonstrate the process in the steps below.
📌 Steps:
- At the very beginning, run beginning steps from the previous methods to open the Visual Basic editor and insert the code into the worksheet.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Left(Target.Address, 3) = "$B$" Then
Range("C" & Target.Row).Value = Time()
Range("D" & Target.Row).Select
ElseIf Left(Target.Address, 3) = "$D$" Then
Range("B" & Target.Row + 1).Select
End If
Application.EnableEvents = True
End Sub
⚡ Code Breakdown:
Here, I will explain the VBA code shown in the picture below.
- To start, use the If Else statement and the Address property to move from column C to column D
- Next, enter the current time using the Time function
- Then, jump to column E and select the E5 cell.
- Finally, move to the next line in the C6 cell using Range.Select method.
- Following this, proceed to the C5 cell >> insert the following expression.
="("&B5&")"
In this equation, the B5 cell refers to the ID which is 123ABC8462.
- In turn, press the ENTER key to automatically insert the Time in the D5 cell and select the E5 cell.
- Next, type in “OK” in the Status column and hit the ENTER key to move to the C6 cell in the next line.
Finally, repeat the same process for the other cells and the results should look like the image given below.
Read More: How to Use Barcode Scanner for Inventory in Excel (with Easy Steps)
How to Add a Barcode to a Cell in Excel?
Adding a barcode to a cell in Excel is quite straightforward, so let’s go through the process of inserting barcodes in detail.
📌 Steps:
- First of all, download the IDAHC39M font >> next install the font on your computer >> then, restart Microsoft Excel on your computer.
- Second, select the B5:B13 range of cells >> press the CTRL + 1 keys to launch the Format Cells dialog box.
- Following this, choose the Text option >> hit the OK button.
- Afterwards, go to the C5 cell and type the following expression into the Formula Bar.
="("&B5&")"
Here, the B5 cell indicates the ID which is 123ABC8462.
- Now, use the Fill Handle Tool to copy the formula into the cells below.
- Additionally, select the C5:C13 range >> choose the IDAHC39M font from the list.
Consequently, your results should appear like the picture shown below.
Admittedly, I have skipped the detailed process of making Barcodes in Excel. You may explore it if you wish.
Read More: How to Add Barcode Font in Excel (Installation and Application)
Practice Section
We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.
Conclusion
Thus, I hope this article helps you understand how to fix the barcode scanner not going to the next line in Excel. If you have any queries, please leave a comment below. Also, if you want to read more articles like this, you can visit our website ExcelDemy.
Related Articles
- Barcode Scanner Entry to Excel Database with Timestamp (3 Basic Cases)
- How to Create Barcode Without Font in Excel (2 Smart Methods)
- Generate Code 128 Barcode Font for Excel (With Easy Steps)
- How to Use Code 39 Barcode Font for Excel (with Easy Steps)
- Print Barcode Labels in Excel (with 4 Easy Steps)