The Excel 2003 (in XP) application (See Link:https://skydrive.live.com/redir.aspx?cid=106e2d7b5a42e060&resid=106E2D7B5A42E060!955) I am trying to enable for optimal navigation is a vendor add/change form for Supply Chain or A/P to use. My goal is to stipulate a tab or enter key order of flow. The standard way of doing it is not working: CTRL-Left-Click the unlocked input cells in the desired sequence, then turn on worksheet protection. I get to a certain point in the cell sequence and the active cell just toggles between two cells.
So, that's weird, but I was hoping to use a VBA solution, of which I found several online, to enforce the tab order. These have not worked as expected, especially at the "Split" command. By the way, there is some VBA code related to the linked file that is not included with the file, as it resides in my personal.xlsb file, but it is irrelevant to the issue at hand. The latest one is as follows:
Thanks. You Gurus are priceless here because the client will be holding a meeting next Monday to approve the final form and they won't like the hassle if I can't get the navigation to work.
So, that's weird, but I was hoping to use a VBA solution, of which I found several online, to enforce the tab order. These have not worked as expected, especially at the "Split" command. By the way, there is some VBA code related to the linked file that is not included with the file, as it resides in my personal.xlsb file, but it is irrelevant to the issue at hand. The latest one is as follows:
HTML:
Dim asTab() As String
Dim iTab As Long
Dim nTab As Long
Dim bInit As Boolean
Sub TabInit()
' set tab order here THIS IS WHERE DEBUG SHOWS PROBLEMS
asTab = Split("E3", "E4", "E5", "E6", "E7", "E8", "E9", "C9", "E10", "E12", "E13", "E14", "E15", "E16", "E17", "E18", "E19", "E20", "H12", "H13", "H14", "H15", "H16", "H17", "H18", "H19", "E21", "E22", "E23", "E24", "E25", "F27", "F28", "F28", "C29", "F29", "I29", "I30", "C30", "F31", "F32", "I33", "F34", "C35", "E37", "E38", "E39", "E40", "I37", "I38", "I39", "I40", "I41", "D52", "I4", "I5", "I6", "I7", "I8", ",")
' The original code showed this format: ("D1,F1,D3,F3,D5,F5,D7,F7", ",")
bInit = True
nTab = UBound(asTab) + 1
iTab = 0
TabSetup
End Sub
Sub TabSetup(Optional bClear As Boolean)
Const sForw As String = "{TAB}"
Const sBack As String = "+{TAB}" ' Shift+Tab
If bClear Then ' restore normal operation
Application.OnKey sForw
Application.OnKey sBack
Else
Application.OnKey sForw, "TabForw"
Application.OnKey sBack, "TabBack"
End If
End Sub
Sub PosChange(ByVal Target As Range)
If Not bInit Then
TabInit
Else
On Error Resume Next
iTab = WorksheetFunction.Match(Target(1, 1).Address(False, False), asTab, 0) - 1
If Err Then iTab = (iTab + 1) Mod nTab
On Error Goto 0
End If
TabMove iTab
End Sub
Sub TabForw()
TabMove (iTab + 1) Mod nTab
End Sub
Sub TabBack()
TabMove (iTab + nTab - 1) Mod nTab
End Sub
Sub TabMove(iTab As Long)
Application.EnableEvents = False
Range(asTab(iTab)).Select
End Sub
Thanks. You Gurus are priceless here because the client will be holding a meeting next Monday to approve the final form and they won't like the hassle if I can't get the navigation to work.