Solved

Control TAB (Tabbing) Direction

Posted on 2011-03-23
5
428 Views
Last Modified: 2012-05-11
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
Comment
Question by:Tosagua
5 Comments
 
LVL 8

Expert Comment

by:ragnarok89
ID: 35201955
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 35201958
You can press Enter instead of Tab
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 500 total points
ID: 35201960
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
 
LVL 6

Expert Comment

by:rbrhodes
ID: 35202874

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
 

Author Closing Comment

by:Tosagua
ID: 35214292
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now