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
(B3)
CITY	STATE	ZIP CODE
(B9)	(E9)
PHONE NO.
(B12)

<tbody>
[TD="colspan: 3"] LAST NAME [/TD]

[TD="colspan: 3"] (E3)[/TD]

[TD="colspan: 4"][/TD]

[TD="colspan: 4"] ADDRESS [/TD]

[TD="colspan: 4"] (B6) [/TD]

[TD="colspan: 4"][/TD]

[TD="colspan: 2"] (F9)[/TD]

[TD="colspan: 4"][/TD]

[TD="colspan: 3"] CONTACT NAME [/TD]

[TD="colspan: 3"] (E12)[/TD]

</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.

 

[Peter_SSs]

I don't have a cloud file sharing service, can you PM your email maybe?

We like to keep everything public in the forum wherever possible - refer to no. 4 of the Forum Rules.

Dropbox is free

 

[John.McLaughlin]

We like to keep everything public in the forum wherever possible - refer to no. 4 of the Forum Rules.

Dropbox is free

ok, I installed dropbox, what is your email address or name?

thanks again

 

[Fluff]

If you upload the file & mark for sharing, you should get a link which you can then post to the thread.

 

[John.McLaughlin]

If you upload the file & mark for sharing, you should get a link which you can then post to the thread.

ahh

https://www.dropbox.com/s/hurk0jl6auc0h3f/peter-ss.xlsm?dl=0

 

[Fluff]

Did you mark the file for sharing, because it's asking me to log-in?

 

[John.McLaughlin]

sorry, it should be shared now?

 

[Peter_SSs]

Something isn't quite right with that link but I did manage to get to the file eventually.


Works perfectly for me so I'm at a loss as to what can be wrong at your end.
If you haven't done so already I would suggest closing right out of Excel, then reopen just that test file & try again.


Edit: I think the link should be this
https://www.dropbox.com/s/hurk0jl6auc0h3f/peter-ss.xlsm?dl=0

 

[Fluff]

Works fine for me as well.

 

[John.McLaughlin]

I disabled the "after pressing enter" in the advanced options, the cell stays selected.

Most puzzling, 2 different machines (same license w/ MS 5 license agreement), and new workbooks?

Thank you for your efforts, I'll update the thread if I ever discover what fixed it.

 

[dmt32]

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?

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