I want to control the order of cell selection during data entry.

[John.McLaughlin]

I have a simple worksheet to enter customer Info. I want to control the order of cell selection and reduce the users need to Tab Right.

1. User enters First Name, press enter VBA to bounce right and enter Last Name (instead of Tab Right)
2. After Last Name is entered, press enter VBA to drop down to address.
3. After address is entered, VBA to drop down to City, VBA bounce right for state, VBA bounce right for zip.
4. Lastly, drop down to phone number, VBA bounce right for contact name

FIRST NAME	LAST NAME
(B3)	(E3)
ADDRESS
(B6)
CITY	STATE	ZIP CODE
(B9)	(E9)	(F9)
PHONE NO.	CONTACT NAME
(B12)	(E12)

<tbody>
</tbody>

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$B$3"
    Range("E3").Activate
Case "$E$3"
    Range("B6").Activate
Case "$B$6"
    Range("B9").Activate
Case "$B$9"
    Range("E9").Activate
Case "$E$9"
    Range("F9").Activate
Case "$F$9"
    Range("B12").Activate
Case "$B$12"
    Range("E12").Activate
End Select
End Sub

I have also added to the top of the Worksheet module;

Private Sub Workbook_Open()
   If ActiveSheet.Name = "ORDER" Then SetOnkey True
End Sub

Nothing seems to work? Any ideas?

Thanks in advance.

 

[John.McLaughlin]

Just for information,
SetOnKey is a code that formed part of a tabbing solution developed by myself & @Jerry Sullivan MVP on this forum sometime ago & would not likely assist you with this particular problem which I now see you have had help resolving

Dave

Thanks Dave, I omitted the SetOneKey command during my process of elimination. It was originally added (per google search) as part of the activate sheet command to force reading the private subs of the data entry sheet.

Unfortunately, I never got the data entry code to work. Since it does seem to work for others, I will also attempt this on the family computer, and my tablet.

I appreciate the info update, and if I figure out what prevented the code from working on my installation, I will post the remedy for others.

 

[dmt32]

Thanks Dave, I omitted the SetOneKey command during my process of elimination. It was originally added (per google search) as part of the activate sheet command to force reading the private subs of the data entry sheet.

Unfortunately, I never got the data entry code to work. Since it does seem to work for others, I will also attempt this on the family computer, and my tablet.

I appreciate the info update, and if I figure out what prevented the code from working on my installation, I will post the remedy for others.

No worries, the tabbing solution was a slightly more complex approach for OPs who wanted to use the Tab key & arrow keys to move around a specified range.

Appreciate you have been ably assisted by others here & resolved - I had typed out another way of writing your code which may be of interest

Option Base 1
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim arr As Variant, m As Variant
    arr = Array("B3", "E3", "B6", "B9", "F9", "B12", "E12")
    m = Application.Match(Target.Address(0, 0), arr, 0)
    If Not IsError(m) Then m = IIf(m = UBound(arr), 1, m + 1): Range(arr(CLng(m))).Activate
End Sub

Solution only apply if Target Address is in the Array & should cycle back to the start address

Dave

 

[John.McLaughlin]

No worries, the tabbing solution was a slightly more complex approach for OPs who wanted to use the Tab key & arrow keys to move around a specified range.

Appreciate you have been ably assisted by others here & resolved - I had typed out another way of writing your code which may be of interest

Option Base 1
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim arr As Variant, m As Variant
    arr = Array("B3", "E3", "B6", "B9", "F9", "B12", "E12")
    m = Application.Match(Target.Address(0, 0), arr, 0)
    If Not IsError(m) Then m = IIf(m = UBound(arr), 1, m + 1): Range(arr(CLng(m))).Activate
End Sub

Solution only apply if Target Address is in the Array & should cycle back to the start address

Dave

Nice code, if I can get this to work, I will implement that!

I toggled Cursor Movement Logical/Visual in the Advanced options, and still nothing.