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:



    What needs to change in order to stay in sequence, even if a cell within the array is left unchanged?


    Thanks.

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


    I think this will do what you want.

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • 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?

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

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

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


    Thank you and Happy New Year![hr]*[/hr] Auto Merged Post;[dl]*[/dl]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.

  • 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?

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • 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?

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

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

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


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


    Change

    Code
    1. iNew = WorksheetFunction.Match(Target.Address(False, False), aTabOrd, 0) - 1


    to

    Code
    1. iNew = WorksheetFunction.Match(Target(1, 1).Address(False, False), aTabOrd, 0) - 1

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

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

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

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

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

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: Force Tab Order Of Cells


    Worksheet module:


    Code module:

    [SIZE=1]Entia non sunt multiplicanda sine necessitate.[/SIZE]

  • Re: Force Tab Order Of Cells


    Quote from shg

    A particular tab order? How would you do that without code?

    My bad, I thought the non-contiguous cell order flowed from left-to-right and top-to-bottom.

  • Re: Force Tab Order Of Cells


    SHG - I've replaced the old sheet code you provided with the new sheet code. I've also added the module code. I've ammended the tab order in the module code you provided. When I attempt to tab, select a cell, etc. I get a Compile Error: Wrong number of arguments or invalid property assignment. When debugged, the error points to the TabInit Sub at the following code:


    Code
    1. asTab = Split ("D1, U1, AG1, E3, E6, E7, E8, E9, C10, Q10, _
    2. D11, Q11, D12, Y6, Y7, Y8, Y9, W10, AK10, X11, AK11, X12, _
    3. E13, Q13, Z13, AG13, D14, Q14, AG14, F15, O15, AA15, AI15, _
    4. E16, M16, S16, AA16, AI16, C17, G17, N17, D18, E19, C20, E21, _
    5. G22, P22, F23, A24, A25, A26, A27, A28, A29, AD19, S20, U20, AD20, _
    6. S21, U21, AD21, S22, U22, AD22, S23, U23, AD23, S24, U24, AD24, S25, _
    7. U25, AD25, S27, U27, AD27, S28, U28, AD28, S29, U29, AD29, AI32, _
    8. AF35, C31, L31, C32, N32, F34, D35, G36, G37, D38, F50, T40, AI40, _
    9. AI42, AI43, AI44, AI45, AI47, AI48, AI49, AI50", ",")


    What did I do wrong in this section of code? I can't find the answer in the help menu of the VBE.