reorganising tab ordering

haydnc

New Member
Joined
Apr 16, 2002
Messages
10
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
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top