Scan multiple barcodes into a single Excel cell, each on a separate line

**AvalonNYC** · 12-16-2014, 10:51 PM

A library has an Excel sheet that's used as a packing list for books to be loaned to other libraries. Each row represents a single box being packed. The cells of that row have info such as Recipient, Shipment number, Box number, etc.

In one of the columns they'd like to scan the barcoded "Acquisition Number" of each of the several books that will go in that box, such that each scanned number will appear on a new line in that cell.

But when they scan a barcode, Excel moves to the next row, since the scanner is programmed to terminate the scanned data with ENTER.

They cannot reprogram the scanners to remove the ENTER, as they are used for other apps.

How can we get Excel to:

If we're in column C
    When Excel detects CR/LF
        If the cell was not empty
            Append a new line to the contents of the cell
        Append the scanned data to the contents of the cell
        Stay in that cell ready for the next scan
Then they can press an arrow key, if required, to get out of the cell.

-- adTHANKSvance

**karedog** · 12-17-2014, 04:18 AM

Hi,

Place this code in worksheet module :

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim cell As Range, lastInput As Variant
  Set cell = Target.Cells(1, 1)
  If cell.Column = Columns("C").Column Then
     Application.EnableEvents = False
       lastInput = cell.Value
       Application.Undo
       If lastInput <> "" Then
          If cell.Value = "" Then
             cell.Value = lastInput
          Else
             cell.Value = cell.Value & vbLf & lastInput
          End If
       End If
     Application.EnableEvents = True
  End If
End Sub

But after this sub is running, any attempt to clear/delete the cell will fail because off the logic run in the code, so another code is needed to do this.

Place this code in standard code module :

Sub ForceClearContent()
  Application.EnableEvents = False
    Selection.ClearContents
  Application.EnableEvents = True
End Sub

You then can call this sub to clear the cell in affected column C.

Regards

Scan multiple barcodes into a single Excel cell, each on a separate line

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Scan multiple barcodes into a single Excel cell, each on a separate line

Re: Scan multiple barcodes into a single Excel cell, each on a separate line

Thread Information

Users Browsing this Thread

Similar Threads

Scan barcodes, replace cell with next one after print action

[SOLVED] Using a value in a single cell to determine multiple data validations from a separate work

For a column of cells, separate the text in a single cell across multiple columns

Link Word Multiple Line Data into Excel Single Cell

[SOLVED] How to Import multiple-line addresses from Word into single cell?

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions