Tab Order of Cells

Worksheet cells don’t have a tab order like controls on a userform. One way that you can define a tab order is to unprotect the cells that you want to include and protect the sheet. Excel will jump to the next unprotected cell when the user tabs out of the active cell.

Tabbing through unprotected cells moves from left-to-right, top-to-bottom. If that fits your data entry setup, then you’re lucky. If not, you have to use an event macro to control the next cell selection. The event macro has its own limitations. Here’s one example of an event macro.

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("A1", "C1", "G3", "B5", "E1", "F4")

'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

Using event code like this requires that the user enter something in the cell. It doesn’t matter how the user exits the cell, it will still go to the next cell. However, if the user doesn’t “change” the cell, the macro doesn’t fire, so it’s not a true tab order.

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. 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

  4. 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

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

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

  6. 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!

  7. Bruce, it is not hard event code.

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

  8. 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?

  9. 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

  10. 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

  11. 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

  12. 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

  13. 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

  14. 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

  15. @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

  16. 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?

  17. 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.

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

  19. 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.

  20. 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

  21. 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

  22. 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

  23. 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

  24. 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

  25. 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.

  26. @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.


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

Leave a Reply

Your email address will not be published.