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.
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.
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.
That’s cool Mike. How do you late bind the scripting runtime library, do you know?
found it
CreateObject(“Scripting.Dictionary”)
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
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
Zik, you can do that just by changing the options in excel.
When pressing the enter button it can go either down or right.
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!
Bruce, it is not hard event code.
Send me a copy of the workbook, I will take a crack … bob at xldynamic dot com
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?
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
Seems I had some code issues. Fixed it with the help of others.
Fixed Code:
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
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
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
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
Thanks I will give it a try.
Thank you very much. Lot of manual coding but worked like a charm.
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
Jason, I think you want something like this
If Target.Column < 3 Then
Target.Offset(0, 1).Select
Else
Me.Cells(Target.Row + 1, 1).Select
End If
End Sub
@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
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?
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.
I have tried all of these codes now and not a single one of them has worked in Excel 2010. Help!
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.
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
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
this is probably sufficient:
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
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
@Jes
You do not seem to have noticed the solution I posted.
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
@Jes
Did you store this code in the codemodule of the [u]sheet[/u] you are working with ?
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.
you can download a sample file here:
http://www.snb-vba.eu/bestanden/__jump_snb.xlsm
@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.
Download the file.
Open it in Excel, enter some text then push ‘enter’.