Change behaviour of ENTER in selection with VBA

I have a Worksheet_SelectionChange event that selects the row to the left of and including the active cell, as well as the column above and including the active cell, with the active cell remaining the original cell.

Problem: When I type something in the active cell and Enter, instead of moving one cell down (which is what is required) it jumps to the start of the selection, as per normal. How can I programmatically change this behaviour so that the cell immediately below the active cell (i.e. the corner of the selection) becomes the new active cell?

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lngRows As Long, lngCols As Long
Dim CornerCell As Range
lngRows = Selection.Rows.Count
lngCols = Selection.Columns.Count

If lngRows > 1 Or lngCols > 1 Then Exit Sub

Set CornerCell = ActiveCell
Application.EnableEvents = False

Union(Range(Cells(CornerCell.Row, 1), Cells(CornerCell.Row, CornerCell.Column)), _
Range(Cells(1, CornerCell.Column), Cells(CornerCell.Row, CornerCell.Column))).Select
CornerCell.Activate
Application.EnableEvents = True
End Sub

Regards,
Henk Stander

Hi,

Try to change the code to this :

Option Explicit
Dim CornerCell As Range
Dim CrossRange As Range
Private Sub Worksheet_Change(ByVal Target As Range)
  If CrossRange Is Nothing Then
     Application.EnableEvents = False
       Application.Undo
         Set CornerCell = ActiveCell
       Application.Undo
     Application.EnableEvents = True
     CornerCell.Offset(1).Activate
  ElseIf Selection.Address = CrossRange.Address Then
     CornerCell.Offset(1).Activate
  End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Dim lngRows As Long, lngCols As Long

  lngRows = Selection.Rows.Count
  lngCols = Selection.Columns.Count
  If lngRows > 1 Or lngCols > 1 Then Exit Sub

  Set CornerCell = ActiveCell
  Application.EnableEvents = False
    Set CrossRange = Union(Range(Cells(CornerCell.Row, 1), Cells(CornerCell.Row, CornerCell.Column)), _
                           Range(Cells(1, CornerCell.Column), Cells(CornerCell.Row, CornerCell.Column)))
    CrossRange.Select
    CornerCell.Activate
  Application.EnableEvents = True
End Sub

Regards

You are welcome, glad I can help.

Regards

If you open this Excel file, and you don't move the cell selection (so the Worksheet_SelectionChange() is not fired), and change something in current cell, it will give an error.

Regards

I mean if you first open your file, and without moving the pointer to another cell, type something, this is what will be happen :

If the code is :

Private Sub Worksheet_Change(ByVal Target As Range)
  If Selection.Address = CrossRange.Address Then
     CornerCell.Offset(1).Activate
  End If
End Sub

It will raise this error :

Run-time error '91':
Object variable or With block variable not set




But if the code is :

Private Sub Worksheet_Change(ByVal Target As Range)
  If CrossRange Is Nothing Then
  ElseIf Selection.Address = CrossRange.Address Then
     CornerCell.Offset(1).Activate
  End If
End Sub

Then no error will be raised, but then your problem of this case is happened again, press Enter to this cell will just move to another cell of current selection, not going down to below cell. If it is ok for you if the first active cell (and for first time only) this happened, then you can simply delete the code as you did in the sample file.

Regards

Of course, here it is :

Application.EnableEvents = False
  Application.Undo
    Set CornerCell = ActiveCell
  Application.Undo
Application.EnableEvents = True

As we know, Excel doesn't have event like Worksheet_BeforeChange(), we can only detect changes after some changes is made. So we must use tricks to do this.

Say we are examining cell A1, and the current value of this cell is 1.
Now lets say we type 2 in this cell. How can we get the previous value of this cell ?
Worksheet_Change() event only fired after the cell is changed, and we don't have something like Worksheet_BeforeChange() event.

So we undo the changes, now we got the previous value of this cell (that is 1), we save this value to a variable. Now we must revert back the cell to the last state (the cell value is 2). If we do this in the worksheet page, we hit the Redo button, but for VBA, strange enough, we must again called undo. So the second undo is actually a Redo action.

For this case, we want to know the location of the corner cell before the changes is made, so we undo, assign the range to a variable, then undo again (which actually redo) to restore the last state.

Regards