LHradowy
02-15-2005, 10:11 AM
Thank god for this site, I have found a VB script that I set the tab sequence for a Excel spread sheet.
Works great as long as I enter in data in the fields, but if I dont it still tabs from left to right.
How do I only set the tabs to go thru the set tabs even if I dont enter data in that field?
Also, I put two radio buttons in my sheet, how can i set the tab to stop on the radio buttons as well. So all I have to do is hit the space bar to auto fill?
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("B3", "B5", "B7", "B9", "F9", "B11", "F11", "B13", "F13", _
"B15", "B17", "B19", "B21", "B23", "B26", "E26", "B27", "E27", "B29", "E29", _
"B30", "E30", "B31", "E31", "E31", "B34", "C43", "B36", "B38", "B40", "B42")
'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
Works great as long as I enter in data in the fields, but if I dont it still tabs from left to right.
How do I only set the tabs to go thru the set tabs even if I dont enter data in that field?
Also, I put two radio buttons in my sheet, how can i set the tab to stop on the radio buttons as well. So all I have to do is hit the space bar to auto fill?
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("B3", "B5", "B7", "B9", "F9", "B11", "F11", "B13", "F13", _
"B15", "B17", "B19", "B21", "B23", "B26", "E26", "B27", "E27", "B29", "E29", _
"B30", "E30", "B31", "E31", "E31", "B34", "C43", "B36", "B38", "B40", "B42")
'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