Problems with macro, setting "tab order" of unprotected cells

[Foxcole]

I found this macro online for controlling tab order among unlocked
cells in a protected worksheet, and modified it to list the cells in
the tab order I need them, per instructions for the macro:

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("B1", "B3", "B4", "B5", "F1", "F3", "B10", "B12",
"E14", "B16", "F38", "A43", "A44", "A45", "A46", "A47", "A48", "A49",
"A50", "A51", "A52", "A53", "A54", "A55", "A56", "A57", "A58", "A59",
"A60", "F76", "F78")
'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 have two problems.

The first is that this doesn't work reliably. Sometimes after I
unprotect and edit the sheet, protect it again and test it, it will
skip some cells entirely... and the cells it skips can be different
from one test to the next (but always skips the same set of cells
until after the next edit).

The other problem is that I need the macro to work whether or not a
change has been made in any of the cells, which could be different
each time the worksheet is used. This version doesn't do that, and I
am not trained in VBA. I used to be a programmer and can tell what the
macro is doing as I read through it, but have no idea what code I
would need to get it to do what I want.

 

[Foxcole]

Apologies... I just realized I left out a bit of important detail. I'm
using Excel 2003 on Win XP Pro SP3.

 

[Cyrus]

Alright, maybe I can help, I am doing the same thing, the problem with
this code I have is that it cycles through all the referenced cells
before it repeats. THis has two drawbacks, First you have to tell it
exactly every cell to go to and secondly, if you make a mistake, you
have to tab through every cell before you can correct. Trying to find
a fix for this currently. I got the code from here,
http://www.ozgrid.com/forum/showthread.php?t=82272

This is what the code I use looks like, and it does work, with the
limitations listed above.

Dim aTabOrd As Variant
Dim iTab As Long
Dim nTab As Long

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim iNew As Long

If IsEmpty(aTabOrd) Then
aTabOrd = Array("F6", "F7", "U7", "F8", "F9", "F10", "F11",
"F12", "A14", "E14", "J14", _
"O14", "A16", "D16", "A18", "E18", "A21", "L21", "A24", "F24",
"J24", "N24", "C25", "H25", _
"U25", "I26", "A29", "C29", "A31", "H31", "M31", "B33", "H33",
"Z33", "AG33", "E35", "H35", _
"K35", "N35", "Z35", "AG35", "C36", "F36", "Z36", "AG36", "A38",
"G38", "J38", "Z38", "AG38", _
"C39", "AG39", "H40", "AG40", "F41", "O41", "AG41", "A43", "G43",
"AG43", "B45", "E45", "I45", _
"AG45", "B47", "E47", "H47", "K47", "AG47", "E50", "I50", "U50",
"AG50", "E51", "AG51", "G53", _
"I53", "N53", "B55", "E55", "H55", "N55", "U55", "Z55", "G57",
"I57", "N57", "U57", "Z57", "AE57", _
"D59", "U59", "E62", "H62", "N62", "E63", "I63", "M63", "E64",
"I64", "M64", "Q64", "U64", _
"E65", "I65", "E67", "I67", "G69", "Z69", "AD69", "AI69", "Z71",
"AD71", "AI71", "Z72", "AD72", _
"AI72", "A73", "R73", "Z73", "AD73", "AI73", "A74", "R74", "Z74",
"AD74", "AI74", "Z75", "AD75", _
"AI75", "G78", "AC2")

nTab = UBound(aTabOrd) + 1
iTab = 0
Else
On Error Resume Next
iNew = WorksheetFunction.Match(Target.Address(False, False),
aTabOrd, 0) - 1
If Err Then
iTab = (iTab + 1) Mod nTab
Else
iTab = iNew
End If
On Error GoTo 0
End If

Application.EnableEvents = False
Range(aTabOrd(iTab)).Select
Application.EnableEvents = True

End Sub