How to wrap Excel cursor to keep it within a specific range

Question

In Excel, I have three columns:

column1, column2, column3

I am entering data into excel using a barcode scanner that is attached to an IPAD. The barcode scanner sends ENTER after every scan. I believe that I can set excel up such that ENTER would cause the next column to be selected (instead of the next row)

However, I do not know how to have it go to the next row after there is an ENTER detected in column3. Right now I have this:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Column = 3 Then
        If Target.Value = "{enter}" Then
            MsgBox "SDf"
        End If
    End If
End Sub

But Target.Value detects only the string inside the cell, it does not detect what has been pressed.

How do I get the next row to be selected after ENTER is detected in column 3?

Answer 1

You don't need vba code for this.

The easiest way is to unlock the cells in those three columns and lock the rest of the cells. Once done, protect the entire sheet. However when protecting, ensure you uncheck the option called Select Locked Cells See screenshot. The yellow columns are unprotected.

The next step is to set excel so that after the data is entered and Enter key is pressed, the cursor moves to the next column. You can do that (Say in Excel 2010) from the File TAB | Options | Excel Options | Advanced

When the cursor reaches the last column and data is entered and the enter is pressed, the cursor will automatically move to the next row. See snapshot.

HTH

Answer 2

In addition to Siddharth's excellent suggestion, here's an event-based approach:

Private Sub Worksheet_Change(ByVal Target As Range)

    'named (contiguous) range on input sheet
    Const DATA_NAME As String = "DATA"
    Dim rngData As Range, numCols As Long

    If Target.Cells.Count > 1 Then Exit Sub

    Set rngData = Me.Range(DATA_NAME)
    numCols = rngData.Columns.Count

    If Not Intersect(rngData, Target) Is Nothing Then
        If Target.Column < rngData.Columns(numCols).Column Then
            Target.Offset(0, 1).Select
        Else
            Target.Offset(1, -(numCols - 1)).Select
        End If
    End If

End Sub