Custom Tabs

neilsinc

Board Regular
Joined
Jan 20, 2014
Messages
135
Is there a way of selecting a custom tab order on a worksheet?

I want to create a tab order of C12 - H12 - M12 - R12 - W12 - C13 - H13 - M13 etc

I found a solution of sorts, but it only seemed to create the tab order for a certain number of cells. Is there a way of doing it any number of cells?
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

royUK

Well-known Member
Joined
Jul 24, 2002
Messages
1,606
- Select each cell that you want the users to be able to tab to and unlock it by:
-Format Cells. Select the protection tab and uncheck the locked check box.
- Protect the sheet and this should enable the tab key to jump to the next unprotected cell.
 

neilsinc

Board Regular
Joined
Jan 20, 2014
Messages
135
- Select each cell that you want the users to be able to tab to and unlock it by:
-Format Cells. Select the protection tab and uncheck the locked check box.
- Protect the sheet and this should enable the tab key to jump to the next unprotected cell.

Sorry, should've said, that won't work as I have cells inbetween that need to be unlocked.

To give you an idea of what i have, it is a golf scorecard and player one has his first 9 scores in cells c12-c20 and his second 9 scores in e12-e20. Player 2 has his first 9 scores in h12-h20 and his second 9 scores in j12-j20.

I would need the tabbing to go from c12-h12-c13-h13 etc. When they get to the second 9 holes, the tabbing would continue as e12-j12-e13-j13.

Does that make sense?
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,378
Try something like this. You will probably have to modify it further to suit your needs.

Code:
[COLOR=darkblue]Sub[/COLOR] Assign_Tab_Key()
    Application.OnKey "{TAB}", "Custom_Tab_Order"
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    
[COLOR=darkblue]Sub[/COLOR] Restore_Tab_Key()
    Application.OnKey "{TAB}"
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    
Sub Custom_Tab_Order()
    [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] ActiveCell.Column
        [COLOR=darkblue]Case[/COLOR] 3, 8, 13, 18: ActiveCell.Offset(, 5).Select    [COLOR=green]'Next tab order column number C H M R[/COLOR]
        [COLOR=darkblue]Case[/COLOR] 23: Range("C" & ActiveCell.Row + 1).Select     [COLOR=green]'Last Tab order column wrap around to frist column  W-->C[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
[COLOR=darkblue]End[/COLOR] Sub

Assign_Tab_Key traps the tab key press and runs the macro Custom_Tab_Order

Restore_Tab_Key restores the tab key press to normal use.

Custom_Tab_Order
defines the column select order when the tab key is pressed.
 

neilsinc

Board Regular
Joined
Jan 20, 2014
Messages
135
Try something like this. You will probably have to modify it further to suit your needs.

Code:
[COLOR=darkblue]Sub[/COLOR] Assign_Tab_Key()
    Application.OnKey "{TAB}", "Custom_Tab_Order"
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    
[COLOR=darkblue]Sub[/COLOR] Restore_Tab_Key()
    Application.OnKey "{TAB}"
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    
Sub Custom_Tab_Order()
    [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] ActiveCell.Column
        [COLOR=darkblue]Case[/COLOR] 3, 8, 13, 18: ActiveCell.Offset(, 5).Select    [COLOR=green]'Next tab order column number C H M R[/COLOR]
        [COLOR=darkblue]Case[/COLOR] 23: Range("C" & ActiveCell.Row + 1).Select     [COLOR=green]'Last Tab order column wrap around to frist column  W-->C[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
[COLOR=darkblue]End[/COLOR] Sub

Assign_Tab_Key traps the tab key press and runs the macro Custom_Tab_Order

Restore_Tab_Key restores the tab key press to normal use.

Custom_Tab_Order
defines the column select order when the tab key is pressed.

That doesn't seem to work. Unless I'm adding it into the wrong place
 

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,378
Put all of it in a standard code module e.g.; Module1

Run Assign_Tab_Key once to initiate it.

Then select any cell in one of these columns (C H M R W) and press the tab key
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,322
Members
414,053
Latest member
Dual Showman

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
Top