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

 

[Rick Rothstein]

Rick,

Just one question on your method. How about uses that would use this to their advantage? e.g. maliciously? clicking two cells to get out of a tab order would be too easy for some of my clients/projects. lol!

It would however keep the events firing

Again, the thread is too long for me to review in detail, but the impression I got was that only certain keystrokes were to keep the user trapped to the tab order and that, say, a mouse click wouldn't... that is why I provided the "trap door" exit. If you do not want the ability to ever leave the tab order, then change the code to this. Note this is an entirely new structure (I reviewed my original code and "tightened it up" by restructuring it), so delete anything of my previous code from your workbook and use this instead (note the change in modules as well)...

Place this code in a General Module

Public TabOrder As String
Public PreviouslySelectedCell As Range

Place this code in the ThisWorkbook Module

Private Sub Workbook_Open()
  TabOrder = "C10,K4,A13,E7"
  TabOrder = "," & Replace(Replace(TabOrder & "," & Split(TabOrder, ",")(0), "$", ""), " ", "")
  If PreviouslySelectedCell Is Nothing Then
    If Intersect(ActiveCell, Range(Mid(TabOrder, 2))) Is Nothing Then
      Set PreviouslySelectedCell = Range(Split(TabOrder, ",")(Len(TabOrder) - Len(Replace(TabOrder, ",", "")) - 1))
      PreviouslySelectedCell.Select
    Else
      Set PreviouslySelectedCell = ActiveCell
    End If
  End If
End Sub

Place this code in the Worksheet Module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Range(Split(Split(TabOrder, "," & PreviouslySelectedCell.Address(0, 0) & ",")(1), ",")(0)).Select
  Set PreviouslySelectedCell = ActiveCell
End Sub

 

[tcove]

Much appreciation to Jerry and Dave for this solution and code.

I had an issue mentioned somewhere earlier in the thread where my form uses Worksheet_Change(ByVal ..) events to unprotect/protect fields depending upon entries earlier in the form and also to hide entire rows where the content and fields become not relevant due to earlier field responses. The code selects the next cell in the sequence even if it is locked or in a hidden row - which I didn't want.

In case it helps anyone else coming across this thread (or in case in my general ignorance of VBA I have gone about it resolving this in a bad way and my solution needs correcting), the adaption I made to the full code posted by Jerry on page 4 was to make the following "Do While" amendment to the Sub TabRange(ByVal TabDirection As Integer) in the Module code. The intention is to get the code to skip on to the next cell in the sequence if the one targeted is locked or in a hidden row. I limited the skips to 100 tp prevent it being able to get into a never ending loop.

The revised sub I am using is:

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)
   
    Do While Range(vTabOrder(i)).Locked = True Or Range(vTabOrder(i)).EntireRow.Hidden = True And i < 100 And i > 1
        'increment i value based on tabdirection
        i = i + IIf(TabDirection = xlPrevious, -1, 1)
    Loop
    
   '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

 

[TwinDragon]

Ok, New to this site. Hello, all.

The code given on Reply #34 works well to TAB between the cells in a range. And I got it to work.

What I would like to know how to do if it can?

Is there a way to skip a row of cells in a range upon a condition of the value in a cell?

Ex: TAB ORDER is set to tab through Range of B10:F25. Column A is auto filled with dates from top of sheet. Lets say I have a 5 date window, so now A10:A14 have dates. I want to TAB through B10:F14 and skip B15:F25. All based on the condition of Column A. So I can get to the next set of cells in the TAB ORDER. Another date scenario, could be dates in A10:A11 and A13:14, so would need to skip Rows 12, 15-25.

Also, I would like to be able to click on a cell out of the tab order, which the current code accomplishes, but it starts at the beginning of the TAB ORDER. I would like to continue in the next cell in the TAB ORDER. In the example above, if I TAB to F12, then click on a cell outside the TAB ORDER, (like H12), then when I hit TAB next, it will go to B13.

Hope this all make since. Thanks in advance for the help.
Twin

1150115911691183

 

[MDBHR]

Hi Jerry,
I used this code for the tab order on a customer information sheet that I created in a workbook containing multiple other worksheets. It worked wonderfully! Except I'm so brand new to all of this that I cannot figure out why ALL of the worksheets have adopted the tab array that I had intended for just the customer info sheet. Would you be able to help me out with this issue?