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: Oldest
Most helpful Newest Oldest
  1. Kevin Jones 7,265 Reputation points Volunteer Moderator
    2015-03-13T01:33:32+00:00

    You're sort of correct in that we've come "full circle". I prefer to think of it as we have cleaned up the code and are back to solving the specific problem at hand. Remember that we were modifying the ReDim to get it to work for you on the Mac--that last change to the statement is in place and it didn't help. Which further puzzles me.

    Let's simplify the test. Put this code into the ThisWorkbook code module of a new workbook and step through it. Let me know what is written to the Immediate window and if and where it breaks.

    Public Sub Test()

        Dim X As Variant

        X = Array()

        ReDim Preserve X(0 To 0)

        ReDim Preserve X(0 To 1)

        X = Empty

        X = Array()

        Debug.Print LBound(X)

        Debug.Print UBound(X)

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

        Debug.Print LBound(X)

        Debug.Print UBound(X)

    End Sub

    Kevin

    Was this answer helpful?

    0 comments
  2. Anonymous
    2015-03-13T02:47:17+00:00

    I did your test and it crashed when executing "ReDim Preserve X(0 To 1)". It never got to to the point of writing anything.

    What does this tell us?

    Was this answer helpful?

    0 comments
  3. Kevin Jones 7,265 Reputation points Volunteer Moderator
    2015-03-13T03:02:35+00:00

    It tells us we might have a bug in the Mac version of VBA. This is pretty basic stuff and it should work.

    After I finish with this post, I'll take it to the other MVPs and, if they can reproduce the crash, we'll take it to Microsoft. While that is going on I'll work on a solution that does not require ReDim.

    At this point I suspect you probably have a corrupt Excel installation on the Mac. Are you able to re-install Office/Excel on that machine?

    Kevin

    Was this answer helpful?

    0 comments
  4. Kevin Jones 7,265 Reputation points Volunteer Moderator
    2015-03-13T04:00:10+00:00

    Let's try this. It does not have any ReDim Preserve statements.

    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 Count 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

        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

                    Count = Count + 1

                End If

            Next Cell

        Next TabOrderReference

        ReDim mTabOrder(1 To Count)

        Count = LBound(mTabOrder)

        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

                    Set mTabOrder(Count) = Cell

                    Count = Count + 1

                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