[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 522
  • Last Modified:

Control TAB (Tabbing) Direction

We have a simple Excel form (not UserForm), that allows listing information for 4 seperate truckloads.

When you hit TAB the next horizontal (unprotected) cell is selected. However, we want to TAB Down first, and then move to the next column of unprrotected cells.

See attached example.

If it isn't possible, what other alternatives should we explore ?

Tosagua
CPSR-DISPATCH-SHEET.xls
0
Tosagua
Asked:
Tosagua
1 Solution
 
ragnarok89Commented:
Not possible, AFAIK. However, you could use a macro for this. Just start recording your macro, execute the desired actions (tabbing, arrows keys, etc), and then stop recording.

Excel will create a module with the VBA code all done for you that represents your recorded actions. You can then tweak this code to get exactly what you want.
0
 
Saqib Husain, SyedEngineerCommented:
You can press Enter instead of Tab
0
 
zorvek (Kevin Jones)ConsultantCommented:
It is not possible to change the tabbing order without VBA code behind the worksheet.

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
0
 
rbrhodesCommented:

Hi,

If your Enter key is set to move right (as most are) then that isn't an aoption.  If itis set to move down you could use it in this macro but I would use the downa arrow as follows:

//In the ThisWorkbook Module:

Option Explicit

Private Sub Workbook_OpenZ()
    Application.OnKey "{TAB}", "mdown"
End Sub
       
Private Sub Workbook_BeforeCloseZ(Cancel As Boolean)
    Application.OnKey "{TAB}"
End Sub

Private Sub Workbook_Activate()
    Application.OnKey "{TAB}", "mdown"
End Sub

Private Sub Workbook_Deactivate()
    Application.OnKey "{TAB}"
End Sub

and in a standard module:

Sub mdown()
        Application.SendKeys "{DOWN}"
End Sub

Cheers,

dr
0
 
TosaguaAuthor Commented:
I am still working on this, but i am going to be gone for a while, and i don't want the question abandoned.

Obviously Microsoft needs to add another feature. But I think this going to work.

Thank you for the extensive effort that you put into this answer.

Tosagua
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now