Establishing enter order on a protected sheet

Anonymous
2012-08-27T22:02:30+00:00

Is it possible to establish the order the cursor goes from beginning to end while entering values into a protected sheet?

Thanks,

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Kevin Jones 7,265 Reputation points Volunteer Moderator
2012-08-27T22:21:21+00:00

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

Was this answer helpful?

20+ people found this answer helpful.
0 comments

43 additional answers

Sort by: Newest
Most helpful Newest Oldest
  1. Kevin Jones 7,265 Reputation points Volunteer Moderator
    2015-03-12T21:50:44+00:00

    I see the problem now. The code was modified halfway through this thread to accommodate more cells in the tab sequence. Let's start over with this code.

    In the worksheet code module:

    Public Function TabOrder() As Variant

        TabOrder = Array( _

            "C3", "A1",  _

            "D4:E5", "B2" _

        )

    End Function

    In the ThisWorkbook module:

    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 Variant

        Dim TabOrderReference As Variant

        Dim Index As Long

        Dim Cells As Range

        Dim Cell As Range

        Dim LowerBound As Long

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

        On Error Resume Next

        TabOrder = ActiveSheet.TabOrder

        On Error GoTo 0

        If IsEmpty(TabOrder) Then Exit Sub

        ' Save tab sequence

        mTabOrder = Array()

        For Each TabOrderReference In TabOrder

            Set Cells = ActiveSheet.Range(TabOrderReference)

            For Each Cell In Cells.Cells

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

                    LowerBound = LBound(mTabOrder)

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

                    Set mTabOrder(UBound(mTabOrder)) = Cell

                End If

            Next Cell

        Next TabOrderReference

        ' 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

    Kevin

    Was this answer helpful?

    0 comments
  2. Anonymous
    2015-03-12T18:48:13+00:00

    Hi Kevin,

    You are correct. For some reason it is exiting the EnableTabbing subroutine even though I have TabOrder is defined in the Worksheet1 subroutine. After the "If TabOrder Is Nothing Then Exit Sub" line it jumps to "End Sub" in "Private Sub Workbook_SheetActivate(ByVal Sh As Object)". I don't understand why, though, since I have the TabOrder code in Sheet1, which I make the active sheet to run the test.

    Here's the code I have:

    [In Sheet1:]

    Public Function TabOrder() As Variant

        TabOrder = Array( _

            "C4", "C6", "C8", "C9", "C11", "C12", "C13", "C16", "F7", "G7", _

            "H7", "F8", "G8", "H8", "F9", "G9", "H9", "F10", "G10", "H10", "F11", "G11", _

            "H11", "C24", "C25", "C26", "G27", "G28", "G29" _

        )

    End Function

    [In ThisWorkbook:]

    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

        Dim LowerBound As Long

        ' 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

                LowerBound = LBound(mTabOrder)

                ReDim Preserve mTabOrder(LowerBound 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 if 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

    Was this answer helpful?

    0 comments
  3. Kevin Jones 7,265 Reputation points Volunteer Moderator
    2015-03-12T06:14:55+00:00

    The code above works at my house.

    I propose something else is amiss. Did you enable macros?

    Put the Stop back in and see if the code is being executed.

    Kevin

    Was this answer helpful?

    0 comments
  4. Anonymous
    2015-03-11T23:23:21+00:00

    Hi Kevin,

    I inserted that code in place of the Private Sub Enable Tabbing routine. I also removed the Stop. Excel doesn't crash but the macro doesn't function. It appears to have no effect. I also tried it on my Windows machine under Excel 2003 and got the same result

    • the macro doesn't function.

    What should we do next?

    Was this answer helpful?

    0 comments