Taborder code trouble

[RobAbel]

Private Sub Worksheet_Change(ByVal Target As Range)
'Rob Abel
'050315 0319
Dim taborder As Variant
Dim i As Long
Application.EnableEvents = False
taborder = Array("d3", "I3", "v3", "l5", "v5", "e7", "n7", "t7", "i9", "q9", "w9", _
"c10", "o10", "c16", "i16", "s17", "e19", "d21", "c23", "i23", "k23", "q19", "p21", "o23", "u23", "w23", "d25", "l25", "s25", "e27", "k27", "o27", "t27", "d31", "j31", "n31", "t31", "f33", "f37", "j37", "m37", "f39", "j39", "m39", "f41", "j41", "q36", "q38", "n40", "s40", "q44")
i = -1
On Error Resume Next
i = Application.WorksheetFunction.Match(Target.Address(0, 0), taborder, 0) - 1
On Error GoTo 0
If i = -1 Then GoTo skip
If i < UBound(taborder) Then Range(taborder(i + 1)).Select Else Range(taborder(0)).Select
skip:
Application.EnableEvents = True
End Sub


Help!

I am using the above code to navigate through a form in ms excel 03 for a sales department. Problem is that if you wish to skip one of these cells you can’t just use the TAB key. You have to hit spacebar followed by the tab key. Also the "SHIFT+TAB" function for reversing within the tab order is not working. Any ideas?

Thanks

 

[RobAbel]

Originally I was using them for formatting reasons. If you think it would fix the problem I could re-work the spacing and avoid merged cells.

On a side note: Do you think it’s possible to work in the whole Shift+Tab thing to go backwards on the array?

Thanks

 

[Andrew Poulsom]

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Static OldCell As Range
    Dim taborder As Variant
    Dim i As Integer
    Dim TabNext
    Dim TabPrevious
    If OldCell Is Nothing Then
        Set OldCell = Range("D3")
    End If
    taborder = Array("d3", "I3", "v3", "l5", "v5", "e7", "n7", "t7", "i9", "q9", "w9", _
"c10", "o10", "c16", "i16", "s17", "e19", "d21", "c23", "i23", "k23", "q19", "p21", "o23", "u23", "w23", "d25", "l25", "s25", "e27", "k27", "o27", "t27", "d31", "j31", "n31", "t31", "f33", "f37", "j37", "m37", "f39", "j39", "m39", "f41", "j41", "q36", "q38", "n40", "s40", "q44")
    Application.EnableEvents = False
    On Error Resume Next
    i = WorksheetFunction.Match(Target.Address(0, 0), taborder, False)
    If Err = 0 Then
        Application.EnableEvents = True
        Exit Sub
    Else
        Err.Clear
    End If
    On Error GoTo 0
    i = WorksheetFunction.Match(OldCell.Address(0, 0), taborder, False)
    If i > UBound(taborder) Then
        If Target.Address = OldCell.Offset(0, -1).Address Then
            Range(taborder(i - 2)).Select
        Else
            Range(taborder(0)).Select
        End If
    Else
        If Target.Address = OldCell.Offset(0, -1).Address Then
            If i = 1 Then
                Range(taborder(UBound(taborder))).Select
            Else
                Range(taborder(i - 2)).Select
            End If
        Else
            Range(taborder(i)).Select
        End If
    End If
    Set OldCell = ActiveCell
    Application.EnableEvents = True
End Sub

 

[mqarep]

Was this ever solved?

Was this ever solved? I have the exact same problem the original poster describes. I must have the ability to click out of sequence and have the cursor stay in that cell.

Thanks.