Controlling the tab order on a protected sheet?

richrr31

New Member
Joined
Aug 25, 2002
Messages
18
Is there a way to control the order that the TAB key goes in when in a protected sheet? It currently goes from Left to Right, then Up to Down. I would like to select the order in which it hits the unprotected cells. Is this possible?
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Hi Rich,

This is easy to do if you are willing to use a VBA macro. You can use Application.OnKey to cause the Tab key to call your macro instead of the usual action. Then in your macro, look at which cell is selected and based on this decide which cell to select next.

Don't forget to set the Tab key back when done.
 
Upvote 0
Hi again Rich,

Here's some sample code to show you what I mean. The following code assumes the unprotected cells are A5, B4, C4 and D1, and makes the tab order "unnatural" C4 > B4 > D1 > A5 > C4 ...

This code should go into a standard macro module. To initialize it, run the SetTabKey macro. Note that I set the definitions of the arrow and tab keys. I also provided a procedure (ResetTabKey) to undo the new tab and arrow key definitions.

Sub SetTabKey()
'sets the arrow and tab keys
Application.OnKey "{TAB}", "NextCell"
Application.OnKey "{DOWN}", "NextCell"
Application.OnKey "{RIGHT}", "NextCell"
End Sub

Sub NextCell()
'Go to next cell in pseudo-tab-sequence
Select Case Selection.Address
Case [C4].Address: [B4].Select
Case [B4].Address: [D1].Select
Case [D1].Address: [A5].Select
Case [A5].Address: [C4].Select
End Select
End Sub

Sub ResetTabKey()
'resets the arrow and tab keys to their default settings
Application.OnKey "{TAB}"
Application.OnKey "{DOWN}"
Application.OnKey "{RIGHT}"
End Sub

The following code should be added to the worksheet's event code module. It implements the new tab sequence for using the Enter key. Note that it just calls the NextCell macro.

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Select
NextCell
End Sub

Let me know if this isn't clear.
 
Upvote 0
Hi Rich,

The first three macros, SetTabKey, NextCell, and ResetTabKey should be pasted into a standard macro module. To do this, go to the Visual Basic Editor (VBE) using keyboard Alt-TMV, and then create a blank macro module (Alt-IM) and paste the code into the VBE code pane. The new tab sequence will go into effect right after running the SetTabKey macro (Alt-TMM > Select SetTabKey > Run).

The Worksheet_Change macro should be pasted into the worksheet's event code module. To do this, right-click on the worksheet's tab, select View Code, and paste the code into the VBE code pane.

Excellerate!

Damon
 
Upvote 0
OK, I figured that out, but here's my next question. I need to have different tab orders for 4 different sheets in the workbook. I made 4 separate NextCell<sheetname> routines, and 4 different SetTabKey<sheetname> routines, but how do I make it so that whichever sheet is active, the correct SetTabKey for that sheet runs?
 
Upvote 0
OK, I figured that out, but here's my next question. I need to have different tab orders for 4 different sheets in the workbook. I made 4 separate NextCell<sheetname> routines, and 4 different SetTabKey<sheetname> routines, but how do I make it so that whichever sheet is active, the correct SetTabKey for that sheet runs?
 
Upvote 0
Hi again Rich,

To get the appropriate SetTabKey for each sheet, put each worksheet's SetTabKey code in that worksheet's Activate event. This is an event that runs when the worksheet is activated. Example:

Private Sub Worksheet_Activate()
'sets the arrow and tab keys
Application.OnKey "{TAB}", "NextCell"
Application.OnKey "{DOWN}", "NextCell"
Application.OnKey "{RIGHT}", "NextCell"
End Sub

Private Sub NextCell()
'Go to next cell in pseudo-tab-sequence
Select Case Selection.Address
Case [C4].Address: [B4].Select
Case [B4].Address: [D1].Select
Case [D1].Address: [A5].Select
Case [A5].Address: [C4].Select
End Select
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Select
NextCell
End Sub

Private Sub Worksheet_Deactivate()
'resets the arrow and tab keys
Application.OnKey "{TAB}"
Application.OnKey "{DOWN}"
Application.OnKey "{RIGHT}"
End Sub

The NextCell routine for each sheet can go directly into the worksheet's event code module as well, so that each sheet has its own NextCell routine--they do not conflict because they are Private to the corresponding worksheet's event module. Note that I included the Change event, since it also would refer to that worksheet's NextCell macro. It is also a good idea to use the deactivate event (as shown) to reset the arrow and tab keys whenever you leave a particular worksheet so that you don't leave Excel's keys assigned when the user goes on to other worksheets or workbooks.

Damon
 
Upvote 0
Thank you very much. It's looking very good. One other question...I also have PreviousCell commands using the up and left keys to go back on the sheet. Do I need to put that in the:

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Select
NextCell
PREVIOUSCELL
End Sub
 
Upvote 0

Forum statistics

Threads
1,223,288
Messages
6,171,223
Members
452,391
Latest member
BHG

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