Tab Order

[thicks]

I have a simple excel invoice sheet that I am working on for a friend. He wants the tab order changed while in protected mode; I have unlocked the cells that he wants to fill in. I will be glad to email the spreadsheet out to anyone willing to look at it. I am far from a VB guy, so any help would be great. I read Anne Troy article on this and downloaded her example, but could not get it to work for me on my sheet.

These are the directions I followed:
On the sheet for which you want to set the tab order, right-click the sheet tab and hit View Code. This opens the Visual Basic Editor (VBE).
Paste the code into the code window that appears at right.
Edit the Array line in the code to suit your tab order.
Hit the Save diskette and close the VBE.
Save and close your Excel workbook.

Here are two of the codes I tried:


Private Sub Worksheet_Change(ByVal Target As Range)


Dim aTabOrder As Variant
Dim i As Long

‘Set the tab order of input cells
aTabOrder = Array ("B1?, "B2?, "B3?, "H1?, "H2?, "H3?, "C6?, "G6?, "B7?, "B8?, "B9?, "E9?, "G9?, "A11?, "B11?, "C11?, "I11?, "A12?, "A15?, B15?, "H15?, "A16?, "B16?, "H16?, "A17?, "B17?, "H17?, "A18?, "B18?, "H18?, "A19?, "B19?, "H19?, "A20?, "B20?, "H20?, "A21?, "B21?, "H21?, "A22?, "B22?, "H22?, "A23?, "B23?, "H23?, "A24?, "B24?, "H24?, "I26?, "I28?, "A26?)

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

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)

Dim aTabOrd As Variant
Dim i As Long

'Set the tab order of input cells
aTabOrd = Array("B1", "B2", "B3", "H1", "H2", "H3", “C6”, “G6”, “B7”, “B8”, “B9”, “E9”, “G9”, “A11”, “B11”, “C11”, “I11”, “A12”, “A15”, B15”, “H15”, “A16”, “B16”, “H16”, “A17”, “B17”, “H17”, “A18”, “B18”, “H18”, “A19”, “B19”, “H19”, “A20”, “B20”, “H20”, “A21”, “B21”, “H21”, “A22”, “B22”, “H22”, “A23”, “B23”, “H23”, “A24”, “B24”, “H24”, “I26”, “I28”, “A26”)

'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

I will be glad to email the spreadsheet out to anyone willing to look at it. I am far from a VB guy, so any help would be great.

Thanks,
Tray

[frank_m]

Hi Thicks,

Welcome to the Forum.

I located the article your referred to
http://www.vbaexpress.com/kb/getarticle.php?kb_id=209

I made corrections to some typing errors that may have occurred during copy paste.

Looks like you added VBA tags to the 2nd code sample, but not the first.
code here[/ VBA]without the space after /
[vba]Private Sub Worksheet_Change(ByVal Target As Range)

Dim aTabOrd As Variant
Dim i As Long

'Set the tab order of input cells
aTabOrd = Array("B1", "B2", "B3", "H1", "H2", "H3", "C6", "G6", "B7", "B8", "B9", "E9", _
"G9", "A11", "B11", "C11", "I11", "A12", "A15", "B15", "H15", "A16", "B16", "H16", "A17", _
"B17", "H17", "A18", "B18", "H18", "A19", "B19", "H19", "A20", "B20", "H20", "A21", "B21", _
"H21", "A22", "B22", "H22", "A23", "B23", "H23", "A24", "B24", "H24", "I26", "I28", "A26")

'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 More details on what you want to accomplish would be helpful. - You can attach a sample workbook by clicking the toolbar icon that looks like a paperclip.

[thicks]

Frank that worked great!! Thanks!!