Negotiating Cells in Specific Order

[Guest]

I have an array of unlocked cells that constitue a range:
e.g. d7 h7 d8 h8 d9 h9 h10 c12 f12
I would like the TAB and ENT keys to always negotiate the range in an
explicit way.
e.g. in the order: d7 d8 d9 c12 f12 h7 h8 h9 h10

Is there a relatively elegant way of doing this?
I'm building up a monster of a module to take care of this
(involving Worksheet_SelectionChange procedure
Case statements and
keeping track of the "previous" selection.)
My intuition tells me I'm missing something.... Any help?

 

[Leith Ross]

Hello PaulD,

If by elegant you mean something in Excel that allows you to control
the tab order of the cells, then the answer is no. The Enter key can be
set to go up, down left or right when pressed. The Tab key can only go
left or right. If a worksheet were like a UserForm where the Tab Order
could be specified life would be good. As far I as know you are taking
the only approach possible.

Sincerely,
Leith Ross

 

[NickHK]

Paul,
This seems to work.
<Module Code>
Public TabCells As Variant
Public CurrentCell As Long
Public Const CellOrder As String = "A1,B2,C1,B1,A2,C2"
</Module Code>
<ThisWorkbook code>
Private Sub Workbook_Open()
TabCells = Split(CellOrder, ",")
CurrentCell = LBound(TabCells)
End Sub
</ThisWorkbook code>
<Worksheet code>
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False
If CurrentCell = UBound(TabCells) Then
CurrentCell = LBound(TabCells)
Else
CurrentCell = CurrentCell + 1
End If
Range(TabCells(CurrentCell)).Select
Application.EnableEvents = True
End Sub
</Worksheet code>

NickHK

 

[Guest]

Thanks Nick
I thought I'd tried something like that, but on review I see I made some
bone-headed mistakes. Also I'm waiting for updated references from Amazon
(currently refering to Webb's '96 book). I'm gonna; i. use ur example, and
ii. fool w/ it and
apply to muli-pages in a Wb.