Tab Order in excel form

[magpie2000k]

Hi all and im new here so please be easy n me.

I have been googling and trying for a while to find my answer but im ot getting anything to work.

I have a spread sheet that has four dstinct boxes and need to be able to tab between them in order which happens to be down the page.

I have protected it and it tabs to my required cells but right to left then the next on right and then left again

I want it just to tab down the page.

I have never used VB and as such all of those options I have seen posted I cant get to work.

Please help

kind regards

Jon

 

[dmt32]

This code won't win any prizes but may do what you want:

Place the following code in your worksheets code page (right click tab >View Code)

Private Sub Worksheet_Activate()
TabRange 1
Application.OnKey "{TAB}", "TabRange"
End Sub

Private Sub Worksheet_Deactivate()
Application.OnKey "{TAB}"
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.OnKey "{TAB}", "TabRange"
End Sub

This code must go in a standard module (right click tab > view code >Insert>Module)

Sub TabRange(Optional ByVal startcell As Integer = 0)
    Dim sTarget As Range
    Dim sTabOrder As Variant
    Dim i As Long
    Set sTarget = ActiveCell
    
    'Set the tab order of input cells - change ranges as required
    sTabOrder = Array("A1", "A5", "A7", "A9", "C1", "C5", "C7", "C9")
     
   'ensure 1st range is selected when sheet activated
    If startcell = 1 Then Range(sTabOrder(LBound(sTabOrder))).Select: Exit Sub
    
    'Loop through the array of cell address
    For i = LBound(sTabOrder) To UBound(sTabOrder)
        'cell in array
        If sTabOrder(i) = sTarget.Address(0, 0) Then
            'cell last in array
            If i = UBound(sTabOrder) Then
                'Select first cell in array
                Range(sTabOrder(LBound(sTabOrder))).Select
            Else
                'Select next cell in array
                Range(sTabOrder(i + 1)).Select
            End If
        End If
    Next i
End Sub

Hope works ok for you

Dave

 

[L. Howard]

For those following this thread, I found this code on a google search, modified it slightly and applied it to an example workbook emailed me by the OP.

The OP was pleased with it.

I would offer that the code speaks of Tab Order and names an array as such. However, in real life the use of the Enter key is actually required to make it function properly.

dmt32 offered some code, which I did not test, but I suspect the function allows the Tab key to be used, which the OP actually asked for.

Regards,
Howard

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
 'Tab order

 Dim aTabOrder As Variant
 Dim i As Long

 'Set the tab order of input cells
 aTabOrder = Array("G7", "G9", "G11", "G13", "F19", "F21", "F23", "F25", "F27", _
                   "F29", "F31", "F33", "P7", "P9", "O11", "P11", "Q11", "R11", _
                   "P14", "R14", "P17", "R17", "Q19", "P25", "P30", "Q30", "R30")

 '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

 

[James Snyder]

Ooops! Slow refresh...