Establishing enter order on a protected sheet

Not without some VBA code.

Excel does not support a custom tabbing order. Even when the worksheet is protected the tab order between the unlocked cells cannot be changed. The solution below implements a custom tabbing order on specific worksheets in a workbook. The solution consists of adding code to the ThisWorkbook module and to each worksheet module that will use custom tabbing. The code added to a worksheet module both defines the tab order and indicates that custom tabbing is in effect.

Note that the code does not take effect until the workbook has been closed and opened, another workbook has been activated, or another worksheet has been activated.

The worksheet code has one function:

[Begin Code Segment]

Public Function TabOrder() As Range

    Set TabOrder = [C3,A1,D4:E5,B2]

End Function

[End Code Segment]

The tab sequence is from left to right as specified in the set of ranges. When more than one cell is specified for one element such as "D4:E5" then the tabbing order follows the normal tabbing order inside that range. The order as specifed in the above example is C3->A1->D4->E4->D5->E5->B2.

When activated, the TAB key activates the next cell in the tab order while SHIFT+TAB activates the previous cell. If the Excel application option "Move selection after Enter" is enabled (Tools->Options->Edit tab) then ENTER and SHIFT+ENTER also move the active cell in the tab order.

Add the code below to the ThisWorkbook module. If any of the routines in the code below conflict with existing routines then merge the code such that the code below runs unhindered by any existing code. None of the code below has to be customized.

[Begin Code Segment]

Private mTabOrder As Variant

Private Sub EnableTabbing()

' Enables custom worksheet tabbing only if the worksheet module has exposed the

' property or function TabOrder.

    Dim TabOrder As Range

    Dim Index As Long

    Dim Cell As Range

    ' Determine if the active worksheet has exposed the TabOrder property or function

    On Error Resume Next

    Set TabOrder = ActiveSheet.TabOrder

    On Error GoTo 0

    If TabOrder Is Nothing Then Exit Sub

    ' Save tab sequence

    mTabOrder = Array()

    For Each Cell In TabOrder

        If Cell.Address = Cell.MergeArea(1, 1).Address Then

            ReDim Preserve mTabOrder(LBound(mTabOrder) To UBound(mTabOrder) + 1)

            Set mTabOrder(UBound(mTabOrder)) = Cell

        End If

    Next Cell

    ' Install key overrides

    Application.OnKey "{TAB}", "ThisWorkbook.MoveToNextTabLocation"

    Application.OnKey "+{TAB}", "ThisWorkbook.MoveToPreviousTabLocation"

    ' Only override ENTER key of application option MoveAfter Return is set on

    If Application.MoveAfterReturn Then

        Application.OnKey "~", "ThisWorkbook.MoveToNextTabLocation"

        Application.OnKey "+~", "ThisWorkbook.MoveToPreviousTabLocation"

        Application.OnKey "{ENTER}", "ThisWorkbook.MoveToNextTabLocation"

        Application.OnKey "+{ENTER}", "ThisWorkbook.MoveToPreviousTabLocation"

    End If

End Sub

Private Sub DisableTabbing()

' Reset all TAB and ENTER key overrides to use default functionality

    Application.OnKey "{TAB}"

    Application.OnKey "+{TAB}"

    Application.OnKey "~"

    Application.OnKey "+~"

    Application.OnKey "{ENTER}"

    Application.OnKey "+{ENTER}"

End Sub

Private Sub Workbook_Activate()

' The workbook activate event is invoked when a workbook is opened or

' activated.

    EnableTabbing

End Sub

Private Sub Workbook_Deactivate()

' The workbook deactivate event is invoked when a workbook is deactivated or

' closed.

    DisableTabbing

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

' The sheet activate event is invoked when a worksheet is activated.

    EnableTabbing

End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

' The sheet deactivate event is invoked when a worksheet is deactivated.

    DisableTabbing

End Sub

Private Sub MoveToNextTabLocation()

' Activate the next tab location after the active cell. If the current active

' cell is not in the tab sequence then activate the first cell in the tab

' sequence.

    Dim SelectNextCell As Boolean

    Dim Index As Long

    For Index = LBound(mTabOrder) To UBound(mTabOrder)

        If SelectNextCell Then

            mTabOrder(Index).Activate

            Exit Sub

        End If

        If mTabOrder(Index).Address = ActiveCell.Address Then SelectNextCell = True

    Next Index

    mTabOrder(LBound(mTabOrder)).Activate

End Sub

Private Sub MoveToPreviousTabLocation()

' Activate the previous tab location before the active cell. If the current active

' cell is not in the tab sequence then activate the last cell in the tab

' sequence.

    Dim SelectNextCell As Boolean

    Dim Index As Long

    For Index = UBound(mTabOrder) To LBound(mTabOrder) Step -1

        If SelectNextCell Then

           mTabOrder(Index).Activate

           Exit Sub

        End If

        If mTabOrder(Index).Address = ActiveCell.Address Then SelectNextCell = True

    Next Index

    mTabOrder(UBound(mTabOrder)).Activate

End Sub

[End Code Segment]

Kevin