33 thoughts on “Tab Order of Cells”

1. Nice idea for worksheet-based form-filling!

   Of course, my inbuilt rewrite process promptly kicked in… I like to find ways to remove nested loops and Ifs where possible, so I came up with this possible alternative:

   Option Explicit

   Private Sub Worksheet_Change(ByVal Target As Range)

   Dim dictTabOrder As Dictionary

   Set dictTabOrder = JumpList(Array(“A1?, “C1?, “G3?, “B5?, “E1?, “F4?))

   If dictTabOrder.Exists(Target.Address(0, 0)) Then
   Me.Range(dictTabOrder.Item(Target.Address(0, 0))).Select
   End If

   End Sub

   Private Function JumpList(list As Variant) As Dictionary

   Dim idx As Long

   Set JumpList = New Dictionary

   With JumpList
   For idx = LBound(list) To UBound(list) – 1
   .Add list(idx), list(idx + 1)
   Next
   .Add list(UBound(list)), list(LBound(list))
   End With

   End Function

   … it needs the Dictionary object from the Microsoft Scripting Runtime (scrrun.dll) which I used in preference to a Collection because it has that handy .Exists() function.

   The first part can be shortened to

   Private Sub Worksheet_Change(ByVal Target As Range)

   With JumpList(Array(“A1?, “C1?, “G3?, “B5?, “E1?, “F4?))

   If .Exists(Target.Address(0, 0)) Then
   Me.Range(.Item(Target.Address(0, 0))).Select
   End If

   End With

   End Sub

   … since the Dictionary object we create does not need to be declared explicitly. If the list never changed, of course, we could build the Dictionary once, in Worksheet_Activate, say.

2. That’s cool Mike. How do you late bind the scripting runtime library, do you know?

3. found it

   CreateObject(“Scripting.Dictionary”)

4. Cool Mike !

   I always use the MATCH function for that…

   If IsNumeric(Application.Match(Target.Address(0, 0), aTabOrder, 0)) Then
   ‘is in the array
   End If

5. I am using a digital gage, with SPC Cable and connection to my computer. My application requires me to measure click a button on the gage and the measurment will be input in excel, the problem i’m having is that i want the active cell selection to move to the next horizontal cell, instead of cell below. In other words if i input data in a cell and then hit enter i want it to work as if i hit tab.

   Thanks

6. Zik, you can do that just by changing the options in excel.

   When pressing the enter button it can go either down or right.

7. This is all greek to me and maybe I am in the wrong forum. My boss is old fashioned an still does scheduling by hand (stubby pencil). I created a spreadsheet for him using four cells and a diagonal line across two of them (top right (D7)/lower left (C8)) to look like the form he is using. Begin shift time is in top left (C7) and end time in lower right (D8) and I have a bazillion on the page to make up two weeks (14 columns)worth of work and up to 30 employees (Rows). Each day for each employee is 4 cells as described above. When I Tab or Enter from C7 I would like for the next cell to be D8 and then C9 and then D10 and so on until I reach the end of the two weeks and then start over at C9 and then D10 for the next employee. Is this possible with a third grade education and having already had 7 beers? If not, will $1.40 get one of you to do it for me? I didnt think so. Help, please!

8. Bruce, it is not hard event code.

   Send me a copy of the workbook, I will take a crack … bob at xldynamic dot com

9. Tabbing through doesn’t seem to work for Excel 2010 like it did for Excel 2003.

   When I tab through, it will only go through a few of the unprotected cells. When I move the cursor (click or arrow key) to one of the other unprotected cells, it will then proceed to the rest of them. But starting from the top it again only cycles through a few cells.

   Glitch? Feature to be turned off?

10. I have a simple excel invoice sheet that I am working on for a friend. He wants the tab order changed while in protected mode; I have unlocked the cells that he wants to fill in. I will be glad to email the spreadsheet out to anyone willing to look at it. I am far from a VB guy, so any help would be great.
    These are the directions I followed:
    On the sheet for which you want to set the tab order, right-click the sheet tab and hit View Code. This opens the Visual Basic Editor (VBE).
    Paste the code into the code window that appears at right.
    Edit the Array line in the code to suit your tab order.
    Hit the Save diskette and close the VBE.
    Save and close your Excel workbook.

    Here are two of the codes I tried:

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim aTabOrder As Variant
    Dim i As Long

    ‘Set the tab order of input cells
    aTabOrder = Array (“B1?, “B2?, “B3?, “H1?, “H2?, “H3?, “C6?, “G6?, “B7?, “B8?, “B9?, “E9?, “G9?, “A11?, “B11?, “C11?, “I11?, “A12?, “A15?, B15?, “H15?, “A16?, “B16?, “H16?, “A17?, “B17?, “H17?, “A18?, “B18?, “H18?, “A19?, “B19?, “H19?, “A20?, “B20?, “H20?, “A21?, “B21?, “H21?, “A22?, “B22?, “H22?, “A23?, “B23?, “H23?, “A24?, “B24?, “H24?, “I26?, “I28?, “A26?)

    ‘Loop through the array of cell address
    For i = LBound(aTabOrder) To UBound(aTabOrder)
    ‘If the changed cell is in the array
    If aTabOrder(i) = Target.Address(0, 0) Then
    ‘If the changed cell is the last array element
    If i = UBound(aTabOrder) Then
    ‘Select the first cell in the array
    Me.Range(aTabOrder(LBound(aTabOrder))).Select
    Else
    ‘Select the next cell in the array
    Me.Range(aTabOrder(i + 1)).Select
    End If
    End If
    Next i

    End Sub

    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(“B1”, “B2”, “B3”, “H1”, “H2”, “H3”, “C6”, “G6”, “B7”, “B8”, “B9”, “E9”, “G9”, “A11”, “B11”, “C11”, “I11”, “A12”, “A15”, B15”, “H15”, “A16”, “B16”, “H16”, “A17”, “B17”, “H17”, “A18”, “B18”, “H18”, “A19”, “B19”, “H19”, “A20”, “B20”, “H20”, “A21”, “B21”, “H21”, “A22”, “B22”, “H22”, “A23”, “B23”, “H23”, “A24”, “B24”, “H24”, “I26”, “I28”, “A26”)

    ‘Loop through the array of cell address
    For i = LBound(aTabOrd) To UBound(aTabOrd)
    ‘If the cell that’s changed is in the array
    If aTabOrd(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

    I will be glad to email the spreadsheet out to anyone willing to look at it. I am far from a VB guy, so any help would be great.

    Thanks,
    Tray

11. Seems I had some code issues. Fixed it with the help of others.
    Fixed Code:
    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("B1", "B2", "B3", "H1", "H2", "H3", "C6", "G6", "B7", "B8", "B9", "E9", _
    "G9", "A11", "B11", "C11", "I11", "A12", "A15", "B15", "H15", "A16", "B16", "H16", "A17", _
    "B17", "H17", "A18", "B18", "H18", "A19", "B19", "H19", "A20", "B20", "H20", "A21", "B21", _
    "H21", "A22", "B22", "H22", "A23", "B23", "H23", "A24", "B24", "H24", "I26", "I28", "A26")

    'Loop through the array of cell address
    For i = LBound(aTabOrd) To UBound(aTabOrd)
    'If the cell that’s changed is in the array
    If aTabOrd(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

12. Tray do you know if there is a maximum on the cells that can be in the tab order. I have a spreadsheet with 180 cells I need to add to the tab order. I have entered 147 cells and it will not allow me to add any more. I also tried the underscore you have at the end of each row but get a code error.
    Thanks

13. Jim: You may have run into a limit using the line continuation character rather than an array limit. Rewrite you code so that you’re filling the array one by one rather than using the Array function. Like this

    Private Sub Worksheet_Change(ByVal Target As Range)

    Dim aTabOrd(1 To 12) As String
    Dim i As Long

    'Set the tab order of input cells
    aTabOrd(1) = "B1": aTabOrd(2) = "B2": aTabOrd(3) = "B3": aTabOrd(4) = "H1"
    aTabOrd(5) = "H2": aTabOrd(6) = "H3": aTabOrd(7) = "C6": aTabOrd(8) = "G6"
    aTabOrd(9) = "B7": aTabOrd(10) = "B8": aTabOrd(11) = "B9": aTabOrd(12) = "E9"

    'Loop through the array of cell address
    For i = LBound(aTabOrd) To UBound(aTabOrd)
    'If the cell that’s changed is in the array
    If aTabOrd(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

14. Thanks I will give it a try.

15. Thank you very much. Lot of manual coding but worked like a charm.

16. Gentlemen,

    Much like others on this thread, I am attempting to have the tab/enter move between specific cells. However, I do not want it to loop back… instead I want it to keep going. My purpose is for barcode scanning. I need the input of cells to work like this:

    A2, B2, C2
    A3, B3, C3
    A4, B4, C4
    etc.

    Always Collums A, then B, then C… and indefinite for rows… just continuing down to the next row and the next row and the next row etc. Is there a way to do that?

    Thanks for your time.

    The input will just keep going down and down, but always the same three collumns. Make sense? I have been working with the first code posted (by Dick Kusleika)… and I see in his code that he tells it to loop back to the beginning when it reaches the last cell… I want it

17. Jason, I think you want something like this

    Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Column < 3 Then Target.Offset(0, 1).Select Else Me.Cells(Target.Row + 1, 1).Select End If End Sub

18. @Dick’s response to Jason,

    This line of code…

    Me.Cells(Target.Row + 1, 1).Select

    can be replaced with this one…

    Target.Offset(1, 1 – Target.Column).Select

    which, when compared with the first code line inside the If..Then block for “structure”, allows us to see that your posted code can be replaced with this one (a one-liner)…

    Private Sub Worksheet_Change(ByVal Target As Range)
    Target.Offset(-(Target.Column > 2), 1 + Target.Column * (Target.Column > 2)).Select
    End Sub

19. None of the above worked for me…

    All i need to do is tab from B4, D4, F4, H4, B7, D7, F7, T4, V4, X4, Z4, T7, V7, X7,.. and then loop back to the start..

    anyone help with this?

20. I am trying to use Dick’s non array approach on an application report. I have copied it into the pane on the entry sheet, however, I cannot get it to function. Is there another part to get this function? In other postings there has been a function that was entered before the entry module.
    Really new to this so if I sound dumb I guess I am when it comes to this.

21. I have tried all of these codes now and not a single one of them has worked in Excel 2010. Help!

22. I just did it in Excel 2010 and it worked for me. I created a new workbook with one sheet. I right clicked on the sheet tab and chose View Code. I pasted the code from the body of this post into the resulting Code Pane. I entered a value in A1 and it took me to C1. I imagine you have the code in the wrong module, but if you follow the steps that I did it will work for you.

23. I am using the code at the top of this post for a sheet call/expense report. I tab across several unlocked sells and enter data. Code for Monday works fine. Tab from M7, M11, O8 to P7 and continue across the sheet.

    When I come back to Tuesday I want to do the same but M12, M16, O13 to P12 and the rest of the week increasing by 5.

    How do I make this work for the rest of the week??

    Thanks,

    Les

24. I also tried the latest code from Tray in Excel 2010, but it is not working. One thing I suspect might be a problem… can you do this with merged cells? For example, I have C4 and D4 merged. In the code I only put “C4”. Could that be why it is not working?

    Here is my code that I put in Sheet1… can anyone see any mistakes?

    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(“C4”, “C6”, “C7”, “C12”, “C13”, _
    “G4”, “G5”, “G6”, “G7”, “G8”, “G9”, “G10”, _
    “J4”, “J5”, “J6”, “J7”, “J8”, “J9”, “J11”, “J12”, “J13”, _
    “K17”, “K18”, “K19”, “K20”, “K21”, “K22”, “K23”, “K24”, “K25”, “K26”, _
    “A30”, “C30”, “D30”, “E30”, “F30”, “A31”, “C31”, “D31”, “E31”, “F31”, _
    “A32”, “C32”, “D32”, “E32”, “F32”, “A33”, “C33”, “D33”, “E33”, “F33”, _
    “A34”, “C34”, “D34”, “E34”, “F34”, “A35”, “C35”, “D35”, “E35”, “F35”, _
    “A36”, “C36”, “D36”, “E36”, “F36”, “A37”, “C37”, “D37”, “E37”, “F37”, _
    “A38”, “C38”, “D38”, “E38”, “F38”, “A39”, “C39”, “D39”, “E39”, “F39”, _
    “C40”, “E40”, “H30”, “H31”)

    ‘Loop through the array of cell address
    For i = LBound(aTabOrd) To UBound(aTabOrd)
    ‘If the cell that’s changed is in the array
    If aTabOrd(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

    Thanks,
    Jes

25. this is probably sufficient:

    Private Sub Worksheet_Change(ByVal Target As Range)
sn = Split("C4,C6,C7,C12,C13,G4,G55,G6,G7,G8,G9,G10", ",")
Application.Goto Range(sn(Application.Match(Target.Address(0, 0), sn, 0)))
End Sub

26. Ok, so now my code reads like this, and I am still not having any luck. Sorry, I’m new to this…

    ———————————————————–

    Private Sub Worksheet_Change(ByVal Target As Range)
    sn = Split(“C4,C6,C7,C12,C13,G4,G55,G6,G7,G8,G9,G10”, “,”)
    Application.Goto Range(sn(Application.Match(Target.Address(0, 0), sn, 0)))
    End Sub

    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(“C4”, “C6”, “C7”, “C12”, “C13”, _
    “G4”, “G5”, “G6”, “G7”, “G8”, “G9”, “G10”, _
    “J4”, “J5”, “J6”, “J7”, “J8”, “J9”, “J11”, “J12”, “J13”, _
    “K17”, “K18”, “K19”, “K20”, “K21”, “K22”, “K23”, “K24”, “K25”, “K26”, _
    “A30”, “C30”, “D30”, “E30”, “F30”, “A31”, “C31”, “D31”, “E31”, “F31”, _
    “A32”, “C32”, “D32”, “E32”, “F32”, “A33”, “C33”, “D33”, “E33”, “F33”, _
    “A34”, “C34”, “D34”, “E34”, “F34”, “A35”, “C35”, “D35”, “E35”, “F35”, _
    “A36”, “C36”, “D36”, “E36”, “F36”, “A37”, “C37”, “D37”, “E37”, “F37”, _
    “A38”, “C38”, “D38”, “E38”, “F38”, “A39”, “C39”, “D39”, “E39”, “F39”, _
    “C40”, “E40”, “H30”, “H31”)

    ‘Loop through the array of cell address
    For i = LBound(aTabOrd) To UBound(aTabOrd)
    ‘If the cell that’s changed is in the array
    If aTabOrd(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

    ———————————————————–

    Regards,
    Jes

27. @Jes

    You do not seem to have noticed the solution I posted.

28. I’ve adjusted my code to this:

    ——————————————–
    Private Sub Worksheet_Change(ByVal Target As Range)
    sn = Split(“C4,C12,C13,G4,G11,G12,G13,J4,J5,J6,J7,J8,J9,J10,J11,J12,J13”)
    Application.Goto Range(sn(Application.Match(Target.Address(0, 0), sn, 0)))
    End Sub
    ——————————————–

    However, it still tabs from left to right even though in my code I have it set to go to C12 after C4, etc.

    Regards,
    Jes

29. @Jes
    Did you store this code in the codemodule of the [u]sheet[/u] you are working with ?

30. I give up–I cannot get any suggestion to work. This needs to be dummied down more than it is for those who do not know how to use code. I have pasted in your code over and over and even reviewed how to paste in code and cannot even get a macro to be recognized.

31. you can download a sample file here:

    http://www.snb-vba.eu/bestanden/__jump_snb.xlsm

32. @snb
    Your sample is not working for me. Is there something else I need to do in order to enable? I have already unlocked the highlighted cells on your sheet & protected to worksheet.

33. Download the file.
    Open it in Excel, enter some text then push ‘enter’.

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.