row - Excel tab to new line after certain amount of columns

Question

I am wanting to set up an excel spreadsheet for data entry with a barcode scanner. The barcode scanner sends the barcode then a tab OR an enter key depending how its programmed.

Basically I want to set up an excel sheet that we can scan 6 barcodes for each item, with the scanner tabbing to the next column each time, then when it reaches the 6th column the next tab will make it move to a new line for the next product.

I hope this makes sense. It can be done in MS word... e.g if you create a table with 6 columns and push tab 7 times it will move to the next row. I am wanting to do this in Excel. Thank you

Answer 1

The TAB or ENTER keys already trigger the SelectionChange event.

So, this might be a little tidier way of doing the same thing if you don't for some other reaason need to use the Change event instead of the SelectionChange event.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngLastColumn As Range

'Note: The {Tab} or {Enter} key triggers selectionChange event.'
' Modify the address of rngLastColumn as needed. It should be one column beyond
' the last column of your inputs, so if input use columns A:F, then it should
' be Range("G:G").

Set rngLastColumn = Range("G:G")
    If Not Intersect(Target, rngValidColumns.Columns(7)) Is Nothing Then
        'Insert a new row:'
        Target.EntireRow.Offset(1, 0).Insert

        'Select the first cell in the new row'
        cells(Target.Row + 1, 1).Select

    End If

End Sub