Tab order

[Chip Pearson]

Carl,

You can't change the tab order of cells. You can restrict selection to
unlocked cells only using
ActiveSheet.EnableSelection = xlUnlockedCells but you can't change the order
of these cells.

 

[Carl Johnson]

I have a protected worksheet with unlocked cells that I want the user to tab
through in a certain order. How can I accomplish this? Again any help would
be appreciated.

 

[Gord Dibben]

Carl

1. Unlock the cells you want to TAB to then protect the worksheet.

If your unlocked cells are in a left to right, top to bottom series, the TAB
key will move you through them as long as Sheet Protection is enabled.

2. If not in this configuration.....you can do it with a Named Range and no
sheet protection.

Assuming your range of cells to be A1, B2, C3, F4, A2, F1 for example.

Select the Second cell(B2) you want in the range then CRTL + click your way
through the range in the order you wish, ending with the First cell(A1). Name
this range under Insert>Name>Define>OK.

Now click on NameBox(top left corner above row 1 and col A), select the range
name to highlight the range. With these cells selected, you can input data
and Tab or Enter your way through the range in the order you selected.

Note: there is a limit of about 25 - 30 cells to a range using this method due
to a 255 character limit in a named range. Longer sheet names will reduce the
number of cells considerably.

If more needed, you can enter them manually in the "refers to" box.

From Debra Dalgleish.....

The limit is 255 characters in the Name definition. For example, I can
define a range of 46 non-contiguous cells, with the following string:

=$B$2,$D$2,$F$2,$H$2,$J$2,$B$4,$D$4,$F$4,$H$4,$J$4,$B$6,$D$6,$F$6,$H$6,
$J$6,$B$8,$D$8,$F$8,$H$8,$J$8,$B$10,$D$10,$F$10,$H$10,$J$10,$B$12,$D$12,
$F$12,$H$12,$J$12,$B$14,$D$14,$F$14,$H$14,$J$14,$B$16,$D$16,$F$16,$H$16,
$J$16,$B$18,$D$18,$F$18,$H$18,$J$18,$L$3

There is a third method which requires VBA and a Worksheet_Change event. Post
back if that is desirable.

Gord Dibben Excel MVP XL2002

 

[JohnI]

Carl,

I had the same idea as Gord Dibben's second suggestion.

This is just the basic outline, & you'll have to look for any problems.
============================================
Option Explicit

Private Sub Worksheet_Activate()
ActiveWorkbook.Names.Add Name:="namePrev", RefersTo:="=" & _
ActiveSheet.Name & "!" & ActiveCell.Address
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim sNext As String
Dim iFind As Integer
Application.EnableEvents = False
sNext = "$A$1 $J$6 $D$2" '\ This is the TAB order.

iFind = InStr(sNext, Range("namePrev").Address)

If iFind > 0 Then
If iFind + 5 > Len(sNext) Then
iFind = 1
Else
iFind = iFind + 5
End If
Range(Mid(sNext, iFind, 4)).Select
Else
MsgBox "Error - Out of Range"
End If

ActiveWorkbook.Names.Add Name:="namePrev", RefersTo:="=" & _
ActiveSheet.Name & "!" & ActiveCell.Address
Application.EnableEvents = True
End Sub

===============================================