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: Most helpful
Most helpful Newest Oldest
  1. Kevin Jones 7,265 Reputation points Volunteer Moderator
    2014-05-14T16:10:10+00:00

    I forgot that Union re-orders/optimizes the references.

    This will work. Put this in the worksheet code module:

    Public Function TabOrder() As Variant

        TabOrder = Array( _

            "E6", "G6", "E7", "G7", "E8", "G8", "E9", "G9", "E10", "G10", "E11", "G11", _

            "E13", "G13", "E14", "G14", "E15", "G15", "E16", "G16", "E17", "G17", "E18", "G18", _

            "E20", "G20", "E21", "G21", "E22", "G22", "E23", "G23", "E24", "G24", "E25", "G25", _

            "E27", "G27", "E28", "G28", "E29", "G29", "E30", "G30", "E31", "G31", "E32", "G32", _

            "E34", "G34", "E35", "G35", "E36", "G36", "E37", "G37", "E38", "G38", "E39", "G39", _

            "E41", "G41", "E42", "G42", "E43", "G43", "E44", "G44", "E45", "G45", "E46", "G46", _

            "E48", "G48", "E49", "G49", "E50", "G50", "E51", "G51", "E52", "G52", "E53", "G53", _

            "E55", "G55", "E56", "G56", "E57", "G57", "E58", "G58", "E59", "G59", "E60", "G60", _

            "T6", "V6", "T7", "V7", "T8", "V8", "T9", "V9", "T10", "V10", "T11", "V11", "T12", "V12", "T13", "V13", _

            "T15", "V15", "T16", "V16", "T17", "V17", "T18", "V18", "T20", "V20", "T21", "V21", "T23", "V23", _

            "W31", "W32", "W33", "W34", "W35", "W36", "W37", "W38", "W39" _

        )

    End Function

    Put this in the Workbook code 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

        ' 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

                    ReDim Preserve mTabOrder(LBound(mTabOrder) 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?

    3 people found this answer helpful.
    0 comments
  2. Kevin Jones 7,265 Reputation points Volunteer Moderator
    2014-05-14T14:47:49+00:00

    Marc,

    You can format the code in the worksheet code module this way to get more cells in the range:

    Public Function TabOrder() As Range

        Set TabOrder = Union( _

            [C3,A1,D4:E5,B2,...], _

            [D20:D22,X40,...], _

            [...] _

        )

    End Function

    Kevin

    Was this answer helpful?

    1 person found this answer helpful.
    0 comments
  3. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  4. Anonymous
    2012-08-28T02:15:24+00:00

    Kevin,

    Click on a cell

    Hold down the ctrl key and click on additional cells in a very random way. 

    Now continue to hit the tab key or the enter key to navigate the cells in the same order they were selected  (cells must remain selected).   (shift Tab or shift enter reverses the path)

    Now with the cells still selected, go to the name box and type in a name for the select cells.  So if the selection gets unselected, you can select this name in the name box and the area will be reselected with knowledge of the tab order of the cells.

    This seems to fall apart if I unlock those cells, then protect the sheet and allow only selection of the unprotected cells.  But if I protect the sheet and allow selection of all cells, then it works as it does on an unprotected sheets.  Now the arrow keys can cause the range to be unselected - so I don't dispute this would take come additional vba code, but it might, as I suggested, allow you to simplify your code.  (you could disable the arrow keys using the old application.OnKey method)

    --

    Regards,

    Tom Ogilvy

    Was this answer helpful?

    0 comments