VBA Express : Excel - Set tab order for a worksheet during data entry

Excel

Set tab order for a worksheet during data entry

Ease of Use

Intermediate

Version tested with

97,2000,2002,2003

Submitted by:

byundt

Description:

Cursor automatically jumps from one specified cell to the next specified cell as user hits Tab or Enter keys. Cells do not need to be contiguous.

Discussion:

When a worksheet is used as a "form", it may be desirable to move the cursor from one input cell to the next as the user enters data. The tab order is hard-coded into a Worksheet_SelectionChange sub. This way, a data entry clerk cannot put the data in a cell not designed for data entry. If the user needs to jump out of the tab order, a different sub can be run to toggle the tab order mode off. Running this sub a second time turns the tab order mode back on.

Code:

instructions for use

			 'This sub goes in the code pane for the data entry worksheet
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
     'Establishes tab order for data entry. Hit Enter or Tab keys to jump to the next cell.
     
    Dim TabOrder As Variant, X As Variant 
    Dim addr As String 
    Dim rg As Range, targ As Range 
    If TabOrderFlag = True Then Exit Sub 
     
    TabOrder=Array("A1", "B5", "C10", "B12") 'List your cell addresses in desired tab order here
    For Each X In TabOrder 
        If rg Is Nothing Then 
            Set rg = Range(X) 
        Else 
            Set rg = Union(rg, Range(X)) 
        End If 
    Next 
     
    Set targ = Intersect(rg, Target) 
    rg.Select 
    If targ Is Nothing Then 
        addr = Target.Cells(1, 1).Address(ColumnAbsolute:=False, RowAbsolute:=False) 
        X = Application.Match(addr, TabOrder, 0) 
        If IsError(X) Then Range(TabOrder(LBound(TabOrder))).Activate 
    Else 
        targ.Activate 
    End If 
     
End Sub 
 
 
 'This code goes in a regular module sheet. The Public declaration must go before any subs or functions
Public TabOrderFlag As Boolean 
 
Sub TabOrderMode() 
    TabOrderFlag = Not TabOrderFlag 
End Sub

How to use:

Copy the Worksheet_SelectionChange sub above.
Right-click the data entry sheet tab and choose "View Code" from the pop-up.
Paste the code into the resulting module sheet.
Change the statement beginning TabOrder = Array("A1", "B5", etc. to reflect the data entry cells in your desired order. Each cell must be enclosed in double quotes.
Create a blank module sheet using the Insert...Module menu item.
Paste the TabOrderMode sub and Public declaration for TabOrderFlag in the resulting module sheet.
Press Alt + Q to close the VBA Editor.
Save workbook before any other changes.

Test the code:

Click on any cell in the data entry worksheet. You should see all of the tab order cells selected.
Enter data in the first cell. Click Enter or Tab when you are done entering data in that cell.
To skip a cell, hit the Enter or Tab key.
To go to a particular cell in the tab order, click on it.
To get out of tab order entry mode, ALT + F8 and select the TabOrderMode sub. Click the Run button. You may now select any cell on the worksheet.
To return to tab order entry mode, run the TabOrderMode sub a second time (same instructions as in previous step). You are now restricted to jumping between cells in the tab order.

Sample File:

TabOrder.zip 7.12KB

Approved by mdmackillop

This entry has been viewed 279 times.