[Solved] Barcode Scanner Not Going to Next Line in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

barcode scanner not going to next line in excel


Barcode Scanner Is Not Going to Next Line in Excel: 3 Solutions

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.

barcode scanner not going to next line in excel

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.

Using TAB and ENTER Keys

  • Next, press the TAB key to move to the D5 cell.

press the TAB key

  • Then, type in “OK” to confirm the scanning >> hit the ENTER button to jump to the next line.

press ENTER key

Now, the cursor moves to the next line in the C6 cell, ready to store the next barcode.

barcode scanner not going to next line in excel

  • Similarly, repeat the same procedure for the other cells to obtain the results shown in the image below.

barcode scanner not going to next line in excel fixed with TAB and ENTER keys

Read More: How to Use Barcode Scanner for Inventory in Excel


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:

  • First, navigate to the Developer tab >> click the Visual Basic button.

Automating the TAB and ENTER Keys with VBA Code

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.

Insert code into worksheet

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

VBA code

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.

Code explanation

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

entering formula

  • Now, pressing the ENTER key moves the cursor to the D5 cell.

using ENTER key

  • Afterward, type in OK and hit the ENTER key to move to the next line, i.e. the C6 cell.

moving to next line

Likewise, apply the same process and your output should look like the screenshot shown below.

barcode scanner not going to next line in excel fixed with VBAfixed with automated TAB and ENTER


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

Applying VBA code

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

code explanation

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

type in expression

  • In turn, press the ENTER key to automatically insert the Time in the D5 cell and select the E5 cell.

log time

  • Next, type in “OK” in the Status column and hit the ENTER key to move to the C6 cell in the next line.

barcode scanner not going to next line in excel

Finally, repeat the same process for the other cells and the results should look like the image given below.

barcode scanner not going to next line in excel fixed with VBA code

Read More: Creating Barcode Scanner to Track Check in & Check out Time in Excel


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.

How do I add a barcode to a cell in Excel

  • Following this, choose the Text option >> hit the OK button.

Format Cells option

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

entering formula

  • Now, use the Fill Handle tool to copy the formula into the cells below.

Using Fill Handle

  • Additionally, select the C5:C13 range >> choose the IDAHC39M font from the list.

Changing Font

Consequently, your results should appear like the picture shown below.

barcode scanner not going to next line in excel fixed with VBA code

Admittedly, I have skipped the detailed process of making Barcodes in Excel. You may explore it if you wish.

Read More: Barcode Scanner Entry to Excel Database with Timestamp


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.

Practice Section


Download Practice Workbook

You can download the practice workbook from the link below.


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.


What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo