Help Modifying Tab Order Code

[corporateaccount]

Hello All,

I have come across and used a script I found on this forum. It works well enough if you only want to go through the workbook in the order specified in the array.

The problem arrises when users want to mouse click out of order instead of tabbing. The first click will take them right back to the next cell in the array. they have to click again to get the cell they want. Needless to say, this drives people nuts. (I've tried and failed to interrupt the code to allow for outside clicks).

Here is the code - let me know if my request was not clear.

Dim aTabOrd As Variant
Dim i As Long
'Set the tab order of input cells
If Sheet2.Cells(6, 12).Value = 2 Then
    aTabOrd = Array("AH6", "P10", "P13", "AB15", "AJ15", "A19", "A20", "A21", "A22", "A24", "I24", "A26", "I26", "Q19", "Q20", "Q21", "Q22", "Q24", "Y24", "Q26", "Y26", "AG19", "A29", "AA29", "E30", "H30", "L30", "O30", "R30", "U30", "X30", "AD30", "A32", "F51", "N51", "A129")
ElseIf Sheet2.Cells(6, 12).Value = 1 Then
    aTabOrd = Array("AH6", "P10", "P13", "AB15", "AJ15", "A19", "A20", "A21", "A22", "A24", "I24", "A26", "I26", "Q19", "Q20", "Q21", "Q22", "Q24", "Y24", "Q26", "Y26", "AG19", "A29", "AA29", "A32", "C73", "H73", "P73", "X73", "AD73", "A75", "A76", "A77", "A78", "A80", "F80", "L75", "L76", "L77", "L78", "L80", "Q80", "A82", "W81", "A85", "A86", "A87", "A88", "A90", "F90", "L85", "L86", "L87", "L88", "L90", "Q90", "W85", "W88", "AE88", "W90", "A119", "T119", "Y119", "Z119", "AA119", "AB119", "AD119", "A120", "T120", "Y120", "Z120", "AA120", "AB120", "AD120", "A122", "T122", "Y122", "Z122", "AA122", "A123", "T123", "Y123", "Z123", "AA123", "A129", "K129")
ElseIf Sheet2.Cells(6, 12).Value = 3 Then
    aTabOrd = Array("AH6", "P10", "P13", "AB15", "AJ15", "A19", "A20", "A21", "A22", "A24", "I24", "A26", "I26", "Q19", "Q20", "Q21", "Q22", "Q24", "Y24", "Q26", "Y26", "AG19", "A29", "AA29", "A32", "A95", "H96", "L96", "P96", "S96", "A98", "A99", "Z98", "AC99", "A80", "H100", "L100", "P100", "S100", "A102", "A103", "Z102", "AC103", "J104", "A106", "A109", "A110", "A111", "A112", "A114", "F114", "L109", "N109", "P109", "X109", "L111", "L114", "AB111", "AB112", "AB113", "AE114", "A116", "W116", "A122", "T122", "Y122", "Z122", "AA122", "A123", "T123", "Y123", "Z123", "AA123", "A129")
Else
End If
    For i = LBound(aTabOrd) To UBound(aTabOrd)
If aTabOrd(i) = target.Address(0, 0) Then
    If i = UBound(aTabOrd) Then
        Sheet1.Range(aTabOrd(LBound(aTabOrd))).Select
    Else
        Sheet1.Range(aTabOrd(i + 1)).Select
    End If
End If
    Next i

 

[mikerickson]

Where does that code reside?

Perhaps something like

Dim aTabOrd As Variant
Dim curIndex As Variant

If Sheet2.Cells(6, 12).Value = 2 Then
    aTabOrd = Array("AH6", "P10", "P13", "AB15", "AJ15", "A19", "A20", "A21", "A22", "A24", "I24", "A26", "I26", "Q19", "Q20", "Q21", "Q22", "Q24", "Y24", "Q26", "Y26", "AG19", "A29", "AA29", "E30", "H30", "L30", "O30", "R30", "U30", "X30", "AD30", "A32", "F51", "N51", "A129")
ElseIf Sheet2.Cells(6, 12).Value = 1 Then
    aTabOrd = Array("AH6", "P10", "P13", "AB15", "AJ15", "A19", "A20", "A21", "A22", "A24", "I24", "A26", "I26", "Q19", "Q20", "Q21", "Q22", "Q24", "Y24", "Q26", "Y26", "AG19", "A29", "AA29", "A32", "C73", "H73", "P73", "X73", "AD73", "A75", "A76", "A77", "A78", "A80", "F80", "L75", "L76", "L77", "L78", "L80", "Q80", "A82", "W81", "A85", "A86", "A87", "A88", "A90", "F90", "L85", "L86", "L87", "L88", "L90", "Q90", "W85", "W88", "AE88", "W90", "A119", "T119", "Y119", "Z119", "AA119", "AB119", "AD119", "A120", "T120", "Y120", "Z120", "AA120", "AB120", "AD120", "A122", "T122", "Y122", "Z122", "AA122", "A123", "T123", "Y123", "Z123", "AA123", "A129", "K129")
ElseIf Sheet2.Cells(6, 12).Value = 3 Then
    aTabOrd = Array("AH6", "P10", "P13", "AB15", "AJ15", "A19", "A20", "A21", "A22", "A24", "I24", "A26", "I26", "Q19", "Q20", "Q21", "Q22", "Q24", "Y24", "Q26", "Y26", "AG19", "A29", "AA29", "A32", "A95", "H96", "L96", "P96", "S96", "A98", "A99", "Z98", "AC99", "A80", "H100", "L100", "P100", "S100", "A102", "A103", "Z102", "AC103", "J104", "A106", "A109", "A110", "A111", "A112", "A114", "F114", "L109", "N109", "P109", "X109", "L111", "L114", "AB111", "AB112", "AB113", "AE114", "A116", "W116", "A122", "T122", "Y122", "Z122", "AA122", "A123", "T123", "Y123", "Z123", "AA123", "A129")
Else
End If

curIndex = Application.Match(ActiveCell.Address(False, False, xlA1, False), aTabOrd, 0)

If IsError(curIndex) Then
    Rem activecell is off of the tab order
Else
    Range(aTabOrd(curIndex Mod (1 + UBound(aTabOrd)))).Select
End If