Customizing tab order

[Clif McIrvin]

Excel 2003 on XP Pro

I have a worksheet that functions as a recipie design form. The logical
groupings of unprotected cells on the worksheet do not correspond to
Excel's tab order.

Is there a way to capture "tabbing" out of a cell (as opposed to
"clicking" out of a cell) so that I can use Range("addr").Activate (or
some other method) to specify where to go next?

I considered the WorkSheet_Change event, but quickly realized that only
fires if the cell is actually changed.

 

[Per Jessen]

Hi Clif

You can use the Worksheet_SelectionChange event, just notice that the Dim
statement has to be inserted at the very top of the worksheet module:

Dim OldAdr As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not OldAdr Is Nothing Then
If OldAdr.Address = "$B$2" Then
Range("D4").Select
ElseIf OldAdr.Address = "$D$4" Then
Range("A5").Select
End If
End If
Set OldAdr = ActiveCell
End Sub

Hopes this helps.
....
Per

 

[Clif McIrvin]

Thank you .. I'll be able to make that work.

<sheepish grin>
I was checking for "B2" instead of "$B$2".
</sheepish grin>

After a little experimenting this morning, I see how using OldAdr
resolves testing for the address I just left.

Thanks again.

--
Clif

Hi Clif

You can use the Worksheet_SelectionChange event, just notice that the
Dim statement has to be inserted at the very top of the worksheet
module:

Dim OldAdr As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not OldAdr Is Nothing Then
If OldAdr.Address = "$B$2" Then
Range("D4").Select
ElseIf OldAdr.Address = "$D$4" Then
Range("A5").Select
End If
End If
Set OldAdr = ActiveCell
End Sub

Hopes this helps.
...
Per

 

[Per Jessen]

Thanks for your reply, I am glad you figured it out.

Regards,
Per

Thank you .. I'll be able to make that work.

<sheepish grin>
I was checking for "B2" instead of "$B$2".
</sheepish grin>

After a little experimenting this morning, I see how using OldAdr resolves
testing for the address I just left.

Thanks again.

 

[Clif McIrvin]

For anyone interested:

The basic concept was working great, until I added the complexity of
wanting to be able to go directly to any cell that I clicked into
instead of following the "tab order" imposed by my code in the
SelectionChange event. Here's a snippet of the code I ended up with,
using Application.OnKey instead:

In a standard code module in the workbook:

Private mac As Long 'Merge Area Count

Private Sub TabProc()
Select Case ActiveCell.Address
Case "$B$7", "$B$8", "$B$9"
ActiveCell.Offset(1).Activate
Case Else
With ActiveCell
mac = .MergeArea.Count
.MergeArea.Cells(mac).Next.Select
' using MergeArea properly tabs out of a "Merged Cell"
End With
End Select
End Sub

and, in the Workbook code module:

Private Sub Workbook_Activate()
Application.OnKey "{TAB}", "TabProc"
Application.OnKey "{ENTER}", "TabProc"
Application.OnKey "~", "TabProc"
End Sub

Private Sub Workbook_Deactivate()
Application.OnKey "{TAB}"
Application.OnKey "{ENTER}"
Application.OnKey "~"
End Sub

For my purposes, it is satisfactory to have ENTER and TAB behave
identically.
Also, the workbook in question could have multiple copies of the same
"form" as different worksheets, so I wrote the code to be sheet
independant.

Per, thanks again for getting me started!

--
Clif

Thanks for your reply, I am glad you figured it out.

Regards,
Per