Active Cell does not change with TAB key

Jay S Emmet

New Member
Joined
Nov 10, 2003
Messages
47
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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Oaktree

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,016
Office Version
  1. 365
:unsure: Not sure I understand...

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

Code:
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

Board Regular
Joined
Aug 26, 2006
Messages
82
If you right click on the sheet tab, select "view code", and paste this:

Code:
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

MrExcel MVP
Joined
Jun 20, 2002
Messages
8,016
Office Version
  1. 365
Yep. Good catch.

Or, use the sheet equivalent of:

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

in the sheet module
 

Jay S Emmet

New Member
Joined
Nov 10, 2003
Messages
47
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
 

Forum statistics

Threads
1,141,754
Messages
5,708,317
Members
421,564
Latest member
Ian_20210927

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
Top