reorganising tab ordering

[haydnc]

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

[James]

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

[Tom Schreiner]

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

[haydnc]

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

[MadCyantist]

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