VBA Express : Excel - Set Tab Order of Cells

Excel

Set Tab Order of Cells

Ease of Use

Easy

Version tested with

2000

Submitted by:

Anne Troy

Description:

Sets the tab order of cells without using worksheet protection.

Discussion:

Worksheet protection automatically sets a tab order from left to right and then down. You may want to go down a column, then across and down another column, or some other tab order. With this macro, you just change the tab order array. Worksheet protection still needs to be applied, and cells into which you want to enter data must be unlocked.

Code:

instructions for use

			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("A5", "B5", "C5", "A10", "B10", "C10") 
     
     '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

How to use:

Copy the code above.
Open the workbook in which you want the code to work.
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.

Test the code:

Open the workbook.
Type into an unlocked cell and hit Tab or Enter.

Sample File:

taborder.zip 6.76KB

Approved by mdmackillop

This entry has been viewed 376 times.