Announcement

Collapse
No announcement yet.

Force Tab Order Of Cells

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Force Tab Order Of Cells



    I have input cells on a protected sheet with a tab order array setup. It was originally set up to work when their was a Worksheet Change. Not every cell is going to have data input into it. This is where the problem lies. If a cell is left blank it tabs out of sequence. I changed it to a Worksheet Open Sub but that didn't work.

    Code is as follows:

    Code:
    Private Sub Worksheet_Open(ByVal Target As Range)
         
        Dim aTabOrd As Variant
        Dim i As Long
         
         'Set the tab order of input cells
        aTabOrd = Array("D1", "U1", "AG1", "E3", "E6", "E7", "E8", "E9", "C10", "Q10", _
        "D11", "Q11", "D12", "Y6", "Y7", "Y8", "Y9", "W10", "AK10", "X11", "AK11", "X12", _
        "E13", "Q13", "Z13", "AG13", "D14", "Q14", "AG14", "F15", "O15", "AA15", "AI15", _
        "E16", "M16", "S16", "AA16", "AI16", "C17", "G17", "N17", "D18", "E19", "C20", "E21", _
        "G22", "P22", "F23", "A24", "A25", "A26", "A27", "A28", "A29", "AD19", "S20", "U20", "AD20", _
        "S21", "U21", "AD21", "S22", "U22", "AD22", "S23", "U23", "AD23", "S24", "U24", "AD24", "S25", _
        "U25", "AD25", "S27", "U27", "AD27", "S28", "U28", "AD28", "S29", "U29", "AD29", "AI32", _
        "AF35", "C31", "L31", "C32", "N32", "F34", "D35", "G36", "G37", "D38", "F50", "T40", "AI40", _
        "AI42", "AI43", "AI44", "AI45", "AI47", "AI48", "AI49", "AI50")
         
         '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
    What needs to change in order to stay in sequence, even if a cell within the array is left unchanged?

    Thanks.

  • #2
    Re: Continue In Tab Order Array Regardless Of Changes In Cell Value

    I think this will do what you want.
    Code:
    Dim aTabOrd As Variant
    Dim iTab    As Long
    Dim nTab    As Long
    
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        
        If IsEmpty(aTabOrd) Then
            aTabOrd = Array("D1", "U1", "AG1", "E3", "E6", "E7", "E8", "E9", "C10", "Q10", _
            "D11", "Q11", "D12", "Y6", "Y7", "Y8", "Y9", "W10", "AK10", "X11", "AK11", "X12", _
            "E13", "Q13", "Z13", "AG13", "D14", "Q14", "AG14", "F15", "O15", "AA15", "AI15", _
            "E16", "M16", "S16", "AA16", "AI16", "C17", "G17", "N17", "D18", "E19", "C20", "E21", _
            "G22", "P22", "F23", "A24", "A25", "A26", "A27", "A28", "A29", "AD19", "S20", "U20", "AD20", _
            "S21", "U21", "AD21", "S22", "U22", "AD22", "S23", "U23", "AD23", "S24", "U24", "AD24", "S25", _
            "U25", "AD25", "S27", "U27", "AD27", "S28", "U28", "AD28", "S29", "U29", "AD29", "AI32", _
            "AF35", "C31", "L31", "C32", "N32", "F34", "D35", "G36", "G37", "D38", "F50", "T40", "AI40", _
            "AI42", "AI43", "AI44", "AI45", "AI47", "AI48", "AI49", "AI50")
            nTab = UBound(aTabOrd) + 1
            iTab = 0
        Else
            iTab = (iTab + 1) Mod nTab
        End If
        
        Application.EnableEvents = False
        Range(aTabOrd(iTab)).Select
        Application.EnableEvents = True
    
    End Sub
    Entia non sunt multiplicanda sine necessitate.

    Comment


    • #3
      Re: Continue In Tab Order Array Regardless Of Changes In Cell Value

      Thanks SHG. It does advance with no changes made to the cell the way I'd like it to. However, it does not allow the user to tab backwards in sequence nor click on another cell. It instead immediately activates the next cell in the array. This wouldn't be a problem if the user enters data correctly the first time. If not the user has to cycle through the array until arriving back at the cell he wants to edit. If code were added based on a click event (that being selecting the cell the user wants to edit with the cursor) could it be written to override the array sequence code and then resume the array sequence code from that point?

      Comment


      • #4
        Re: Continue In Tab Order Array Regardless Of Changes In Cell Value

        This will allow the user to select a cell in the tab order and resume from there. I don't know how to support back-tabbing.
        Code:
        Dim aTabOrd As Variant
        Dim iTab    As Long
        Dim nTab    As Long
        
        Private Sub Worksheet_SelectionChange(ByVal Target As Range)
            Dim iNew    As Long
            
            If IsEmpty(aTabOrd) Then
                aTabOrd = Array("D1", "U1", "AG1", "E3", "E6", "E7", "E8", "E9", "C10", "Q10", _
                "D11", "Q11", "D12", "Y6", "Y7", "Y8", "Y9", "W10", "AK10", "X11", "AK11", "X12", _
                "E13", "Q13", "Z13", "AG13", "D14", "Q14", "AG14", "F15", "O15", "AA15", "AI15", _
                "E16", "M16", "S16", "AA16", "AI16", "C17", "G17", "N17", "D18", "E19", "C20", "E21", _
                "G22", "P22", "F23", "A24", "A25", "A26", "A27", "A28", "A29", "AD19", "S20", "U20", "AD20", _
                "S21", "U21", "AD21", "S22", "U22", "AD22", "S23", "U23", "AD23", "S24", "U24", "AD24", "S25", _
                "U25", "AD25", "S27", "U27", "AD27", "S28", "U28", "AD28", "S29", "U29", "AD29", "AI32", _
                "AF35", "C31", "L31", "C32", "N32", "F34", "D35", "G36", "G37", "D38", "F50", "T40", "AI40", _
                "AI42", "AI43", "AI44", "AI45", "AI47", "AI48", "AI49", "AI50")
                nTab = UBound(aTabOrd) + 1
                iTab = 0
            Else
                On Error Resume Next
                iNew = WorksheetFunction.Match(Target.Address(False, False), aTabOrd, 0) - 1
                If Err Then
                    iTab = (iTab + 1) Mod nTab
                Else
                    iTab = iNew
                End If
                On Error GoTo 0
            End If
            
            Application.EnableEvents = False
            Range(aTabOrd(iTab)).Select
            Application.EnableEvents = True
        
        End Sub
        Entia non sunt multiplicanda sine necessitate.

        Comment


        • #5
          Re: Continue In Tab Order Array Regardless Of Changes In Cell Value

          Thank you and Happy New Year!
          Auto Merged Post;

          Well Thanks however, it didn't work. It is acting the same way as the previous code you wrote. Every time I select a cell out of sequence it activates the next cell in the sequence from the cell that was active prior to me selecting a cell. No worries; I'll live with it.
          Last edited by Yzerman19; January 1st, 2008, 04:24. Reason: Auto Merged Doublepost

          Comment


          • #6
            Re: Continue In Tab Order Array Regardless Of Changes In Cell Value

            Hmmm ... don't think so. If you select another cell in the sequence (using the mouse), it resumes from there. If you tab out of a cell, it selects the next cell in the sequence.

            Can you give an example?
            Entia non sunt multiplicanda sine necessitate.

            Comment


            • #7
              Re: Continue In Tab Order Array Regardless Of Changes In Cell Value

              I wish I could. The best I way to describe is as follows:
              When I open the file the first cell active is D1, which is the first cell in the sequence. If, using my mouse, I choose to immediately select cell E6, which is further along in the sequence, E6 becomes active for as long as I hold the mouse button down, but immediately upon release the mouse button D1 becomes active again. I try again to select E6, this time U1 becomes active as it follows D1 in the sequence. Perhaps you should know that other issues have arisen when with my mouse (Logitech TrackMan Wheel). I have in other workbooks coded the scroll area and found that if I use my scroll wheel it ignores the scroll area coding I have implemented. Any correlation to this issue?

              Comment


              • #8
                Re: Continue In Tab Order Array Regardless Of Changes In Cell Value

                Try another mouse -- I just checked it again, and it works fine for me. Or type a cell address in the Names box.
                Entia non sunt multiplicanda sine necessitate.

                Comment


                • #9
                  Re: Continue In Tab Order Array Regardless Of Changes In Cell Value

                  I sent my file to a coworker. He had the same issue. I'll attach the file for your review.
                  Attached Files

                  Comment


                  • #10
                    Re: Continue In Tab Order Array Regardless Of Changes In Cell Value

                    You forgot to tell me you had merged cells ...

                    Change
                    Code:
                            iNew = WorksheetFunction.Match(Target.Address(False, False), aTabOrd, 0) - 1
                    to
                    Code:
                            iNew = WorksheetFunction.Match(Target(1, 1).Address(False, False), aTabOrd, 0) - 1
                    Entia non sunt multiplicanda sine necessitate.

                    Comment


                    • #11
                      Re: Continue In Tab Order Array Regardless Of Changes In Cell Value

                      Sweet! Thanks again. Someday I hope to be at your level. Thanks for the lesson.

                      Comment


                      • #12
                        Re: Continue In Tab Order Array Regardless Of Changes In Cell Value

                        You're welcome, glad it worked for you.

                        I think your basic notion of how to create a custom tab order was clever.

                        Edit: BTW, I figured out how to support back-tabbing if you're still interested ...
                        Entia non sunt multiplicanda sine necessitate.

                        Comment


                        • #13
                          Re: Continue In Tab Order Array Regardless Of Changes In Cell Value

                          You can let excel do the work by unlocking those cells and and only allowing selection of unlocked cells via Worksheet protection.

                          Comment


                          • #14
                            Re: Force Tab Order Of Cells

                            The sheet is locked, and those are the unprotected cells. The point was to enforce a particular tab order.
                            Entia non sunt multiplicanda sine necessitate.

                            Comment


                            • #15


                              Re: Force Tab Order Of Cells

                              No need for any code then.

                              Comment

                              Working...
                              X