Breaking Tab and Enter Key Navigation

So the other day I got a complaint from one of my users: "I really wish you'd fix the Excel tabbing issue in this file."

Now I've sent a considerable amount of time training my users to enter data in worksheets by navigating to the right by pressing the Tab key and then pressing Enter when they are done working with the row. For those of you who don't know this, when you work in this way, Excel will return you to the cell just below the one you started tabbing from. Try this:

• Select cell B1, press Tab 3 times and you'll be in cell E1. Press Enter and you will be returned to cell B2.
• Select cell B1, press Tab 3 times, then press the right arrow key and you'll be in cell F1.  Pressed Enter and you'll be in cell F2. (This is because you started using different keys to navigate, so the tab caching was lost.)

Okay, so this is pretty basic navigation, but I accidentally did something that breaks it.

As a matter of general practice, I apply worksheet protection (with no password) to all of my templates. This is just fine if you leave the default options -- "select locked cells" and "select unlocked cells"-- checked. If you decide to only let users select unlocked cells, however, the keys work like this:

• Select cell B1, press Tab 3 times and you'll be in cell E1. Press Enter and you will be returned to cell E2.

That is E2, not B2 as it was before! (Note that this does assume that at least B1:E2 is unlocked.) Personally, I found this pretty irritating. I've also been able to confirm that this is an issue in Excel 2003 and Excel 2007. I haven't tested any further back than that.

Now, the big question that I'd like to know... Is this a feature or a bug? Does anyone have a good reason for why this scenario would be different?

At any rate, here's a fix:

The following code goes in the ThisWorkbook module:

[vb]Private Sub Workbook_Open()
' Written By: Ken Puls (www.excelguru.ca)
' Purpose   : Capture the Tab key to a specific event
    Application.OnKey "{Tab}", "OnTab"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
' Written By: Ken Puls (www.excelguru.ca)
' Purpose   : Cancel the Tab key override
    Application.OnKey "{Tab}"
End Sub

Private Sub Workbook_SheetSelectionChange( _
    ByVal Sh As Object, ByVal Target As Range)
' Written By: Ken Puls (www.excelguru.ca)
' Purpose   : Evaluate cell movement
    If rngFirstTab Is Nothing Then
        'Not in tabbing mode, do nothing
    Else
        'In tabbing mode
        Application.EnableEvents = False
        'Check if range below last tab cell was selected
        '(assumes that user presed Enter to get there)
        If Target.Offset(-1, 0).Address = rngLastTab.Address Then
            'True, so return to cell below tabbing origin
                rngFirstTab.Offset(1, 0).Select
        End If
      Â
        'Set tabbing origin and last tab to nothing
        Set rngFirstTab = Nothing
        Set rngLastTab = Nothing
        Application.EnableEvents = True
    End If
End Sub[/vb]

And the following code goes in a standard module:

[vb]Public rngLastTab As Range
Public rngFirstTab As Range

Public Sub OnTab()
' Written By: Ken Puls (www.excelguru.ca)
' Purpose   : Override tab movement
'             For use in environements where tab returns are
'             lost (Sheets protected with "Only select unlocked
'             cells.)

    'Record where tabbing started
    If rngFirstTab Is Nothing Then _
    Set rngFirstTab = ActiveCell
  Â
    'Record where tab is going
    Set rngLastTab = ActiveCell.Offset(0, 1)
  Â
    'Activate next cell
    Application.EnableEvents = False
    rngLastTab.Select
    Application.EnableEvents = True
End Sub
[/vb]

One little note here: if you press the down arrow at the end of a string of tabs, it will be treated as if you hit the Enter key. You'll be sent back to the beginning of the row. I suppose that I could have also captured the Enter key's onKey event, but I elected not to bother with this.