Active Cell does not change with TAB key

[Jay S Emmet]

Why is it that the "Real" active cell is always the previous cell when using the "TAB" key. It seems that any other key updates the "Active Cell" address to the current selection, but not the "TAB Key.

 

[Oaktree]

Not sure I understand...

If you right click on the sheet tab, select "view code", and paste this:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox ActiveCell.Address
End Sub

Then, switch back to Excel, press the tab key, and the message box will display whatever the address of the (new) activecell is. Is that not what you expected it to be?

 

[xapie128]

If you right click on the sheet tab, select "view code", and paste this:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
MsgBox ActiveCell.Address
End Sub

Shouldn't that code go in the ThisWorkbook object instead of the Sheet object?

 

[Oaktree]

Yep. Good catch.

Or, use the sheet equivalent of:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox ActiveCell.Address
End Sub

in the sheet module

 

[Jay S Emmet]

I am trying something like this and have noticed the problem.
Dim LastCell As String

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Cells.FormatConditions.Delete
Target.FormatConditions.Add Type:=xlExpression, Formula1:="TRUE"
Selection.FormatConditions(1).Interior.ColorIndex = 35
If Target.Count > 1 Then Exit Sub
Dim NextCell As String
NextCell = ""
Application.EnableEvents = False
Select Case LastCell
Case "$C$8"
NextCell = "$d$8"
Case "$d$8"
NextCell = "$e$8"
Case "$e$8"
NextCell = "$f$8"
Case "$f$8"
NextCell = "$C$9"
Case "$C$9"
NextCell = "$c$10"
Case "$c$10"
NextCell = "$d$8"
Case "$d$9"
NextCell = "$d$10"
Case "$d$10"
NextCell = "$e$8"
Case "$e$9"
NextCell = "$e$10"
Case "$e$10"
NextCell = "$f$8"
Case "$f$9"
NextCell = "$f$10"
Case "$f$10"

Case Else
NextCell = "$C$8"
End Select
If NextCell <> "" Then
Range(NextCell).Activate
LastCell = NextCell
End If
Application.EnableEvents = True
End Sub