Tab and/or enter order: VBA or otherwise

clmish

New Member
Joined
Nov 11, 2008
Messages
15
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:

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.
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

Forum statistics

Threads
1,216,095
Messages
6,128,790
Members
449,468
Latest member
AGreen17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top