VBA Code for Tab order

[kevindow]

How can I create a custom tab order (for example A1-B6-D4 etc...) that is not contingent on a protected worksheet. I have found the following code on-line but I am having some issues with the code. For one it only advances via tab if I enter a change into a given cell. Another problem is if I enter data inside a cell that is not listed in the code it gives an error message. Any suggestions.

Private Sub Worksheet_Change(ByVal Target As Range) Dim aTabOrd As Variant Dim i As Long 'Set the tab order of input cells aTabOrd = Array("A5", "B5", "C5", "A10", "B10", "C10") 'Loop through the array of cell address For i = LBound(aTabOrd) To UBound(aTabOrd) 'If the cell that's changed is in the array IfaTabOrd(i) = Target.Address(0, 0) Then 'If the cell that's changed is the last in the array If i =UBound(aTabOrd) Then 'Select first cell in the array Me.Range(aTabOrd(LBound(aTabOrd))).Select Else'Select next cell in the array Me.Range(aTabOrd(i + 1)).Select End If End If Next i End Sub

 

[Driesumdre]

Hi Jerry & Dave,

Many thanks for your reply, I will check this first thing monday.
Could the problem possibly be in the cell T11, witch is a variable value from an index formula? and not a fixed number?
Should i put in a special paste VBA for this cell?
Many thanks,
André

 

[Jerry Sullivan]

Hi André, The code uses the Value of the cell regardless of whether it has a formula or a constant so pasting special won't affect it.

What happens when you try to use your code as currently written?

 

[Driesumdre]

What happens when you try to use your code as currently written?

Nothing at all, somehow I think I forgot something.

Here is the full code i have.
ThisWorkbook:

'tbv tabvolgorde
Private Sub Workbook_Open()
   If bIsTabOrderValue(ActiveSheet) Then SetOnkey True
End Sub
'tbv tabvolgorde
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   If bIsTabOrderValue(Sh) Then SetOnkey True
End Sub
'tbv tabvolgorde
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
   If bIsTabOrderValue(Sh) Then SetOnkey False
End Sub
'tbv tabvolgorde
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
   If bIsTabOrderValue(ActiveSheet) Then SetOnkey True
End Sub
'tbv tabvolgorde
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
   If bIsTabOrderValue(ActiveSheet) Then SetOnkey False
End Sub

Module:

Option Explicit

Public Function bIsTabOrderValue(ByVal wks As Worksheet) As Boolean
   Dim avValueList As Variant
'--edit this list with the names of the sheets to which a custom tab order
'  should be applied. Each of these sheets should also have a sequence of
'  cell addresses listed in a Case statement in Function GetTabOrder.

   avValueList = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10")
      bIsTabOrderValue = _
      IsNumeric(Application.Match(wks.Name, avValueList, 0))
      
End Function


Public Function GetTabOrder() As Variant
 '  Ver 2 2014 - Dave Timms (aka DMT32) and  Jerry Sullivan
 '--set the tab order of input cells - change ranges as required
 '  do not use "$" in the cell addresses.
 
 Dim sNumber As String
 sNumber = Sheets("Mutatieformulier").Range("T11").Value
 Select Case sNumber
   Case "0"
      GetTabOrder = Array("I7", "G11")
   Case "1"
      GetTabOrder = Array("G11", "G13", "G15", "G16", "G17", "G18", "G19", "G20", "G21", "G22", "G23", "G24", _
         "G27", "G28", "G29", "G33", "G34", "G35", "G36", "G37", "G44", "G45", "G46", "J47", _
         "O44", "O45", "O46")
   Case "2"
      GetTabOrder = Array("G11", "G13", "G14", "G15", "G16", "G17", "G18", "G19", "G20", "G21", "G22", "G23", _
         "G24", "O13", "O14", "O15", "O16", "O17", "G27", "G28", "G29")
   Case "3"
      GetTabOrder = Array("G11", "G13", "G14", "G15", "G16", "G17", "G18", "G19", "G20", "G21", "G22", "G23", _
         "G24", "O13", "O14", "O15", "O16", "O17", "G27", "G28", "G29")
   Case "4"
      GetTabOrder = Array("G11", "G13", "G14", "G15", "G16", "G17", "G18", "G19", "G20", "G21", "G22", "G23", _
         "G24", "O18", "O19", "G27", "G28", "G29")
   Case "5"
      GetTabOrder = Array("G11", "G13", "G14", "G15", "G16", "G17", "G18", "G19", "G20", "G21", "G22", "G23", _
      "G24", "O23", "G27", "G28", "G29")
   Case "6"
      GetTabOrder = Array("G11", "G13", "G14", "G15", "G16", "G17", "G18", "G19", "G20", "G21", "G22", "G23", _
         "G24", "O23", "G27", "G28", "G29", "G33", "G34", "G35", "G36", "G37")
   Case "7"
      GetTabOrder = Array("G11", "G13", "G14", "G15", "G16", "G17", "G18", "G19", "G20", "G21", "G22", "G23", _
         "G24", "G27", "G28", "G29")
   Case "8"
      GetTabOrder = Array("G11", "G13", "G14", "G15", "G16", "G17", "G18", "G19", "G20", "G21", "G22", "G23", _
         "G24", "G27", "G28", "G29", "O29", "O31", "O32")
   Case "9"
      GetTabOrder = Array("G11", "G13", "G14", "G15", "G16", "G17", "G18", "G19", "G20", "G21", "G22", "G23", _
         "G24", "G27", "G28", "G29", "O28", "O29", "O31", "O32")
   Case "10"
      GetTabOrder = Array("G11", "G13", "G15", "G16", "G17", "G18", "G19", "G20", "G21", "G22", "G23", "G24", _
      "G27", "G28", "G29", "G33", "G34", "G35", "G36", "G37", "O13", "O14", "O17", "O29", "O31", _
      "O32", "G44", "G45", "G46", "J47", "O44", "O45", "O46")
   Case Else
      MsgBox "Error: Tab volgorde is niet gespecificeerd voor deze CelWaarde (T11)."
   End Select
End Function


Sub SetOnkey(ByVal state As Boolean)
'  Ver 2 2014 - Dave Timms (aka DMT32) and  Jerry Sullivan
    If state Then
        With Application
            .OnKey "{TAB}", "'TabRange xlNext'"
            .OnKey "~", "'TabRange xlNext'"
            .OnKey "{RIGHT}", "'TabRange xlNext'"
            .OnKey "{LEFT}", "'TabRange xlPrevious'"
            .OnKey "{DOWN}", "'UpOrDownArrow xlDown'"
            .OnKey "{UP}", "'UpOrDownArrow xlUp'"
        End With
    Else
    'reset keys
        With Application
            .OnKey "{TAB}"
            .OnKey "~"
            .OnKey "{RIGHT}"
            .OnKey "{LEFT}"
            .OnKey "{DOWN}"
            .OnKey "{UP}"
        End With
    End If
End Sub

Sub TabRange(ByVal TabDirection As Integer)
'  Ver 2 2014 - Dave Timms (aka DMT32) and  Jerry Sullivan
 Dim vTabOrder As Variant, m As Variant, i As Long

 vTabOrder = GetTabOrder
 On Error Resume Next
 m = Application.Match(ActiveCell.Address(0, 0), vTabOrder, False)
 On Error GoTo ExitSub
 'if activecell is not in Taborder array start at first cell
 If IsError(m) Then
   'goto first cell in array
   m = LBound(vTabOrder)
 Else
   'get corresponding array index
   i = m + LBound(vTabOrder) - 1
   'increment i value based on tabdirection
   i = i + IIf(TabDirection = xlPrevious, -1, 1)
   'ensure stay within array bounds
   If i > UBound(vTabOrder) Then
      i = LBound(vTabOrder)
   ElseIf i < LBound(vTabOrder) Then
      i = UBound(vTabOrder)
   End If
End If
 'select cell based on array element
 Application.EnableEvents = False
 Range(vTabOrder(i)).Select
ExitSub:
 Application.EnableEvents = True
End Sub

Sub UpOrDownArrow(Optional iDirection As Integer = xlUp)

Dim vTabOrder As Variant
Dim lRowClosest As Long, lRowTest As Long
Dim i As Long, iSign As Integer

Dim sActiveCol As String
Dim bFound As Boolean

'--get the tab order from shared function
vTabOrder = GetTabOrder

'--find TabCells in same column as ActiveCell in iDirection
'--  rTest will include ActiveCell

sActiveCol = GetColLtr(ActiveCell.Address(0, 0))

iSign = IIf(iDirection = xlDown, -1, 1)
lRowClosest = IIf(iDirection = xlDown, Rows.Count + 1, 0)

For i = LBound(vTabOrder) To UBound(vTabOrder)
   If GetColLtr(CStr(vTabOrder(i))) = sActiveCol Then
      lRowTest = Range(CStr(vTabOrder(i))).Row
         
   '--find closest cell to ActiveCell in rTest
      If iSign * lRowTest > iSign * lRowClosest And _
         iSign * lRowTest < iSign * ActiveCell.Row Then
         '--at least one cell in iDirection of same columnn
         bFound = True
         lRowClosest = lRowTest
      End If
   End If
Next i

If bFound Then
   Application.EnableEvents = False
   Cells(lRowClosest, ActiveCell.Column).Select
   Application.EnableEvents = True
End If
End Sub

Private Function GetColLtr(sAddr As String) As String
Dim iPos As Long, sTest As String

Do While iPos < 3
   iPos = iPos + 1
   If IsNumeric(Mid(sAddr, iPos, 1)) Then
      Exit Do
   Else
      sTest = sTest & Mid(sAddr, iPos, 1)
   End If
Loop

GetColLtr = sTest
 
End Function

If I use Tab, the direction will be right.
I can not gen my head arount this.
I appreciate your help.

Thanks, André

 

[Jerry Sullivan]

André, This function was intended to be used to quickly determine if an activated sheet is on the list of sheets that have special tab order.

Public Function bIsTabOrderValue(ByVal wks As Worksheet) As Boolean
   Dim avValueList As Variant
'--edit this list with the names of the sheets to which a custom tab order
'  should be applied. Each of these sheets should also have a sequence of
'  cell addresses listed in a Case statement in Function GetTabOrder.

   avValueList = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10")
      bIsTabOrderValue = _
      IsNumeric(Application.Match(wks.Name, avValueList, 0))
      
End Function

Are the names of your sheets simply 0,1,2....?

 

[Driesumdre]

Hi Jerry, Many thanks for your reply. This Clarifies a Lot! I totaly misunderstood this function. In My Case there is only one sheet, I just changed the array in this function to:

 avSheetList = Array("Mutatieformulier")

And it works like a charme! Thanks, thanks and thanks again! I wonder if it would be possible to apply "Shift-Tab" as well in this code? Thanks

 

[99moorem]

Hi,

Sorry for bringing up an older thread, but woud this code work on a protected sheet where the cells in the tab array are unlocked?

I have been using a protected sheet with some unprotected cells to tab through, I have added a few new cells to the right of the original ones (merged so larger roughly 4 rows rather than 1), and now the normal tab method gets stuck in a loop between one of the original cells and these new larger cells.

Thanks for responce's

Marc

 

[Jerry Sullivan]

Hi Marc, Yes, the code should work if all the cells in the tab array are unlocked and the protection is set to allow "Select unlocked cells".

 

[99moorem]

Ok Cool, I will give it a try a little later and let you know.

 

[99moorem]

Hi,

The only problem I seem to be having with this code is because it is always running I can not select any of my buttons to run my code. For example to go back to the main menu? Or to publish the document.

Do you think there is anyway to detect (or catch the error, just standard cant excute in break mode) when a button is clicked and turn the tab order macro off?

Personally I do not know of any events that trigger when a button is clicked. And they would not fire as you are already running code? correct me if wrong.

Any help would be greatly appreciated.

Thanks

Marc

 

[Rick Rothstein]

The only problem I seem to be having with this code is because it is always running I can not select any of my buttons to run my code. For example to go back to the main menu? Or to publish the document.

Do you think there is anyway to detect (or catch the error, just standard cant excute in break mode) when a button is clicked and turn the tab order macro off?

I missed this thread (or at least it did not catch my attention) when it appeared previously. I know that early on the idea of using the Change event was thrown out early on, but I do not see why. As far as I can tell, this Change event code should work fine as long as the cells in the tab order are not locked (and it should not interfere with your button selections)...

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim TabOrder As String
  TabOrder = "C10,K4,A13,E7"
  TabOrder = "," & Replace(Replace(TabOrder & "," & Split(TabOrder, ",")(0), "$", ""), " ", "")
  If InStr(TabOrder, "," & Target.Address(0, 0) & ",") Then
    Range(Split(Split(TabOrder, "," & Target.Address(0, 0) & ",")(1), ",")(0)).Select
  End If
End Sub

Note: The TabOrder variable must be assigned a comma delimited list of addresses and the next line of code must always be executed after the assignment in order to make sure the TabOrder variable ends up the correct format.