PDA

View Full Version : Sleeper: Tab sequence works if enter data, but if I dont...



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

Ken Puls
02-15-2005, 10:41 AM
Hi there, and welcome to VBAX!

I'm not actually sure that you need a VBScript to set your tab order. (Although I don't know your spreadsheet, so you may.) If you change the cells you want the user to use to unprotected (Format Cells|Protection and uncheck the "Locked" checkbox), then protect the sheet, it should move between unprotected cells with the tab key.

Also, I reformatted your code a bit for you. a. I also inserted some line break characters so that no one needs to scroll to see your code.

Cheers,

mdmackillop
02-15-2005, 02:57 PM
Not sure about the radio buttons, but try the following for the basic tabbing.
BTW, I need absolution for sinning in my thoughts when I eventually discovered the repeated "E31" entry :stars: in your list of cells!



Option Explicit
Private Sub Worksheet_SelectionChange(ByVal target As Range)
If target.Cells.Count > 1 Then Exit Sub
DoTab target.Offset(0, -1), True
End Sub

Sub DoTab(ByVal target As Range, Optional Test1 As Boolean)
Dim aTabOrd As Variant
Dim i As Long, Test2 As Boolean
'Test1 and Test2 are set to false later to allow the code to run once only
If Test1 = False And Test2 = False Then
Application.EnableEvents = True
Exit Sub
End If
Test1 = False
'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", "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
'Just to show what's happening
Selection.Interior.ColorIndex = 6
Test2 = True
Else
'Prevent selection change from running when selection is changed
Application.EnableEvents = False
'Select next cell in the array
Me.Range(aTabOrd(i + 1)).Select
Selection.Interior.ColorIndex = 6
'Setting Test2 to False forces exit from sub when it runs again
'from the penultimate line
Test2 = False
'For debbugging and avoidance of CRI in convoluted code.
'MsgBox Selection.Address(0, 0)
Exit For
End If
End If
Next i
'Running the code again resets the Application.EnableEvents
'to true before exiting the sub
DoTab target
End Sub

mdmackillop
02-16-2005, 05:38 PM
One bug: the cell to the right (in this case) of a designated cell is inaccessible!