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