When a cell is selected, move to a different cell

[JM]

I have a large excel 2007 spreadsheet for data entry. My users are highly
accustomed to using Enter rather than Tab to move right across each row from
columns A to V to enter data, so Enter is set to move the active cell Right.
When users get to column V they may or may not enter data, but they need to
return to column A of the next row.

To facilitate data entry, I put the text "go back" in all cells of column W,
and used the code below to move the active cell back to column A of the next
row when a user hits Enter and moves from column V to column W. Users really
like this function. The PROBLEM: If anyone selects more than one cell of a
row or column on any place in the worksheet, run-time error 13 occurs. Any
solutions?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Value = "go back" Then
Cells(Target.Row + 1, 1).Select
End If
End Sub

 

[smartin]

I have a large excel 2007 spreadsheet for data entry. My users are highly
accustomed to using Enter rather than Tab to move right across each row from
columns A to V to enter data, so Enter is set to move the active cell Right.
When users get to column V they may or may not enter data, but they need to
return to column A of the next row.

To facilitate data entry, I put the text "go back" in all cells of column W,
and used the code below to move the active cell back to column A of the next
row when a user hits Enter and moves from column V to column W. Users really
like this function. The PROBLEM: If anyone selects more than one cell of a
row or column on any place in the worksheet, run-time error 13 occurs. Any
solutions?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Value = "go back" Then
Cells(Target.Row + 1, 1).Select
End If
End Sub

Give this a try.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim GoBack As Range
Set GoBack = Range("W:W")
If Not Application.Intersect(Target, GoBack) Is Nothing Then
If Application.Intersect(Target, GoBack).Value = "go back" Then
Cells(Target.Row + 1, 1).Select
End If
End If
End Sub


Actually, this can be simplified. You can remove the "go back" labels in
column W and just test to see if the user hit that column:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim GoBack As Range
Set GoBack = Range("W:W")
If Not Application.Intersect(Target, GoBack) Is Nothing Then
Cells(Target.Row + 1, 1).Select
End If
End Sub


Nice productivity hack, BTW.

 

[JM]

Your simplified approach works great. Thanks.