Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: reorganising tab ordering

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Haydn Cooper
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi everyone,
    I have a protected worksheet that on opening, runs a macro and goes to cell "B11". Using the "Tab" key on the keyboard, the cells that it moves to, ie, the ones I have removed the protection from, are row driven in the order below:
    B11, I11, G12, B14, I14, I7, L7.
    I want to be able to change this order to:
    B11, G12, B14, I11, I14, I7, L7.
    In Tools >Options >Edit, I already have the "Move selection after enter direction" set to "Down" but because the worksheet is protected, it somehow disables this.
    Does anyone have any ideas on how to achieve an ordered columnar tab rather than a row driven one? All assistance gratefully received.
    Kind regards
    Haydn


  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Huntington Beach, CA USA
    Posts
    327
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You may want to expriment with something like this:

    In your sheet module:

    Private Sub Worksheet_SelectionChange(ByVal _
    Target As Excel.Range)
    If Target.Address = "$A$1" Then
    Range ("$C$1").Select
    End IF
    End Sub

    Modify you cell address as needed

    James

  3. #3
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi
    Try this out as well...

    Place the following line into a standard module:

    Public NextTab As Range

    Also into your Workbook Class Module:

    Private Sub Workbook_Open()
    Application.EnableEvents = False
    Set NextTab = Range("B11")
    Sheet1.Range("B11").Select
    Application.EnableEvents = True
    End Sub

    This in your Worksheet Class Module:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    Select Case NextTab.Address
    Case "$B$11"
    Set NextTab = Range("G12")
    Case "$G$12"
    Set NextTab = Range("B14")
    Case "$B$14"
    Set NextTab = Range("I11")
    Case "$I$11"
    Set NextTab = Range("I14")
    Case "$I$14"
    Set NextTab = Range("I7")
    Case "$I$7"
    Set NextTab = Range("L7")
    Case "$L$7"
    Set NextTab = Range("B11")
    End Select
    NextTab.Select
    Application.EnableEvents = True
    End Sub

    I know it's alot of code, but it was the only way I could figure out how to do what you needed...

    Tom

  4. #4
    New Member
    Join Date
    Apr 2002
    Location
    Haydn Cooper
    Posts
    10
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks for your suggestions James and Tom.
    Have gone with Tom's in the end because of the ability to add further cell addresses and also ensure it comes back to my opening cell on close.
    Many thanks again and kind regards
    Haydn

  5. #5
    New Member
    Join Date
    Jul 2002
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Everyone,
    Long time lurker, but new member looking for further development of this theme.

    Working from TsTom's coding, if you had a workbook with 5 seperate sheets and wanted each to have a different tab ordering scheme, what modifications would need to be made to the coding of the Workbook Class Module? Each sheet tab ordering scheme would also begin from a different cell (i.e. not from B11 each time).

    Any assistance would be appreciated. It's absolutely amazing the amount of info I have gained from this site in the past, and I hope to participate more in the future.
    Regards,
    Dave

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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