Tab navigation in a protected worksheet

[rarchamb]

Hi
When I create a sales order sheet, I have a SOLD TO "column" to the left of
a SHIP TO "column". What happens now, is that when I press TAB, I go from
left to right. What I WANT to do is to COMPLETE the data entry for the SOLD
TO "column" (4 lines) AND THEN go to the SHIP TO "column" (1st row) and then
when pressing TAB, be able to navigate that "column" and complete the SHIP TO
data entry process. PLEASE NOTE that this form will be used by ALL of our
sales reps and MANY of our clients, so it HAS to navigate this way to make it
as easy as possible.

Thank you.

 

[Gord Dibben]

Unlock the cells in Column A and B.

Then use the ENTER key set to move down on entry.

You will go down column A then up to B1

Tab key will always move left to right through unlocked cells.


Gord Dibben MS Excel MVP

 

[rarchamb]

Thanks for the reply, Gord.
However, that does not solve the problem. This is a sales order form and
therefore has mulitple entry points, all of which are stacked and staggered,
so pressing ENTER beyond my "last sold to" entry keeps the cursor going down
into the order sheet before it ever gets to the "ship to" column. So if
there's no way to set the order of the entry fields, then I guess I'm
"screwed" with this sheet, short of using an OLE type of program affiliation
with ACCESS or something... aaarrrrggghhhh!

 

[Gord Dibben]

In that case, see Bob Phillips' site for creating a named range for jumping
around.

http://www.xldynamic.com/source/xld.xlFAQ0008.html

Or you could try Anne Troy's taborder event code.

Private Sub Worksheet_Change(ByVal Target As Range)
'Anne Troy's taborder event code
Dim aTabOrd As Variant
Dim i As Long

'Set the tab order of input cells
aTabOrd = Array("A5", "D5", "C7", "E10", "B2", "C10")

'Loop through the array of cell address
For i = LBound(aTabOrd) To UBound(aTabOrd)
'If the cell that's changed is in the array
If aTabOrd(i) = Target.Address(0, 0) Then
'If the cell that's changed is the last in the array
If i = UBound(aTabOrd) Then
'Select first cell in the array
Me.Range(aTabOrd(LBound(aTabOrd))).Select
Else
'Select next cell in the array
Me.Range(aTabOrd(i + 1)).Select
End If
End If
Next i

End Sub

Right-click on the sheet tab and "View Code". Copy/paste the code into that
sheet module. Adjust the aTabOrd Array to suit.

Alt + q to go back to the Excel window.

Enter data in A5, you will be taken to the next cell.in the aTabOrd

Remember..........this is change event code.


Gord

 

[rarchamb]

Thanks once again Gord.
I know that the script you've posted here SHOULD work, but somehow I can't
get it to. I'm either not pasting it in the correct area or I'm not
activating my MACROs (yet I DO choose to run macros when prompted).
Is there a "secret" to this that I'm overlooking??

Thanks

 

[Gord Dibben]

Have you copied the code into the worksheet module by right-click on the sheet
tab and "View Code" to open the module?

What is not working if you have copied it to the correct module?


Gord