Breaking Tab and Enter key navigation

Posted on August 8th, 2007 in Bugs, Excel by Ken Puls

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:

PLAIN TEXT
Visual Basic:
  1. Private Sub Workbook_Open()
  2. ' Written By: Ken Puls (www.excelguru.ca)
  3. ' Purpose   : Capture the Tab key to a specific event
  4.     Application.OnKey "{Tab}", "OnTab"
  5. End Sub
  6.  
  7. Private Sub Workbook_BeforeClose(Cancel As Boolean)
  8. ' Written By: Ken Puls (www.excelguru.ca)
  9. ' Purpose   : Cancel the Tab key override
  10.     Application.OnKey "{Tab}"
  11. End Sub
  12.  
  13. Private Sub Workbook_SheetSelectionChange( _
  14.     ByVal Sh As Object, ByVal Target As Range)
  15. ' Written By: Ken Puls (www.excelguru.ca)
  16. ' Purpose   : Evaluate cell movement
  17.     If rngFirstTab Is Nothing Then
  18.         'Not in tabbing mode, do nothing
  19.     Else
  20.         'In tabbing mode
  21.         Application.EnableEvents = False
  22.         'Check if range below last tab cell was selected
  23.         '(assumes that user presed Enter to get there)
  24.         If Target.Offset(-1, 0).Address = rngLastTab.Address Then
  25.             'True, so return to cell below tabbing origin
  26.                 rngFirstTab.Offset(1, 0).Select
  27.         End If
  28.        
  29.         'Set tabbing origin and last tab to nothing
  30.         Set rngFirstTab = Nothing
  31.         Set rngLastTab = Nothing
  32.         Application.EnableEvents = True
  33.     End If
  34. End Sub

And the following code goes in a standard module:

PLAIN TEXT
Visual Basic:
  1. Public rngLastTab As Range
  2. Public rngFirstTab As Range
  3.  
  4. Public Sub OnTab()
  5. ' Written By: Ken Puls (www.excelguru.ca)
  6. ' Purpose   : Override tab movement
  7. '             For use in environements where tab returns are
  8. '             lost (Sheets protected with "Only select unlocked
  9. '             cells.)
  10.  
  11.     'Record where tabbing started
  12.     If rngFirstTab Is Nothing Then _
  13.     Set rngFirstTab = ActiveCell
  14.    
  15.     'Record where tab is going
  16.     Set rngLastTab = ActiveCell.Offset(0, 1)
  17.    
  18.     'Activate next cell
  19.     Application.EnableEvents = False
  20.     rngLastTab.Select
  21.     Application.EnableEvents = True
  22. End Sub

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.

2 Comments

2 Responses to 'Breaking Tab and Enter key navigation'

Subscribe to comments with RSS or TrackBack to 'Breaking Tab and Enter key navigation'.

  1. Dick Kusleika said,

    on August 9th, 2007 at 9:04 am

    I say it's a bug. Unless a 'Softie can explain why there's a difference, there's no way I'll believe it was designed that way.

  2. Ken Puls said,

    on September 23rd, 2008 at 9:38 pm

    Way late to comment on this, but the Excel team has declared that this is "by design". I can't say that I'll buy it, but there you have it.

Post a comment