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?

Please Login or Register  to view this content.

Regards,
Henk Stander

Hi,

Try to change the code to this :

Please Login or Register  to view this content.

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 :

Please Login or Register  to view this content.

It will raise this error :

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




But if the code is :

Please Login or Register  to view this content.

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 :

Please Login or Register  to view this content.

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