Consulting

Results 1 to 4 of 4

Thread: Sleeper: Tab sequence works if enter data, but if I dont...

  1. #1
    VBAX Newbie
    Joined
    Feb 2005
    Posts
    1
    Location

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

    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

  2. #2
    Moderator VBAX Guru Ken Puls's Avatar
    Joined
    Aug 2004
    Location
    Nanaimo, BC, Canada
    Posts
    4,001
    Location
    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. FYI - if you enclose your code in [vba][/vba] tags then it will be formatted as per the VBIDE to improve readability.. I also inserted some line break characters so that no one needs to scroll to see your code.

    Cheers,
    Ken Puls, CMA - Microsoft MVP (Excel)
    I hate it when my computer does what I tell it to, and not what I want it to.

    Learn how to use our KB tags! -||- Ken's Excel Website -||- Ken's Excel Forums -||- My Blog -||- Excel Training Calendar

    This is a shameless plug for my new book "RibbonX - Customizing the Office 2007 Ribbon". Find out more about it here!

    Help keep VBAX clean! Use the 'Thread Tools' menu to mark your own threads solved!





  3. #3
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    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 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

  4. #4
    Administrator
    VP-Knowledge Base VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    One bug: the cell to the right (in this case) of a designated cell is inaccessible!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •