Solved: Code Selection Change is fired by Enter key

[omp001]

Hi people.
With the Excel configuration set to uncheked "move the selection after 'Enter' " and with the code below into the worksheet module, the code is triggered if, selected any cell, empty or not, I hit 'Enter'. This happens in XL 2003 and XL 2010.
I thought the code would be triggered only after changing the selected cell. (???)
Is this normal that happens?
thanks

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "hello"
End Sub

[Kenneth Hobs]

Working as designed. You are thinking about the Change event. SelectionChange event happens when a new selection is changed from the previous selection.

[Paul_Hossler]

Were you thinking of handling a change in the value of a cell?




Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox "hello"
End Sub


Paul

[omp001]

Ok, thanks to both for answers.
Well, I'm not a VBA expert but my knowledge about it is enough to differentiate what WS events are able to fire WS_SelectionChange and WS_Change.
What is happening is exactly what I reported above. The code,Worksheet_SelectionChange, is triggering as I hit 'Enter'. That's why I am surprised and looking for an explanation.
Thanks again.

[Aussiebear]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MsgBox "hello"
End Sub

When using the above code, by hitting Enter, aren't you effectively forcing a refresh of the sheet? And, because you haven't defined a specific cell or range of cells, I'm assuming the Excel then declares the sheet to be the "Target As Range"?

[omp001]

Thanks Aussiebear
Setting a range, into this range setted, the 'Enter' key also fires the code. Out of range does not.
Based on your explanation then I can conclude that this feature is native in Excel and not a bug in my version. Is it?

[Kenneth Hobs]

See:

Working as designed.

I don't know what out of range means.

You can see what is going on by:

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

[Paul_Hossler]

The code,Worksheet_SelectionChange, is triggering as I hit 'Enter'. That's why I am surprised and looking for an explanation.


That's because you're changing the 'Selection', that is, the cell that is Selected.

If you want to respond to a change in value of a cell, then use the Worksheet_Change event

What is it that you're trying to do?

Paul

[snb]

You might conclude that the changing of the selection doesn't trigger the selection_change event, but the pressing of the Enter key does.
If you do not want this to happen you could use: application.enableevents=false

[omp001]

That's because you're changing the 'Selection', that is, the cell that is Selected.
That´s the doubt: I'm not changing the selection!
The code is firing as I hit 'Enter', WHITHOUT changing the selected cell.

In the code below I've defined a range, as 'Aussiebear' told. and I've inserted 'EnableEvents' as 'snb' suggested. The result keeps the same as in my code at OP.


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Application.Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub
Application.EnableEvents = False
MsgBox Target.Address
Application.EnableEvents = True
End Sub

[Kenneth Hobs]

The doubt is that it is "not" changing selection when you press Enter. You are telling us that if you select A1 by clicking it with the mouse, and then press Enter, that the activecell is then still A1?

My Tools > Options > Advanced setting is to move Right after pressing Enter.

Using your code, it executes as expected. Selecting A1 causes the MsgBox to show the Target address as A1. I then dismiss the MsgBox. I then press the Enter key and the cursor moves to make B1 the activecell selected. The code fires and exits on line one since there is no intersection range as expected.

I don't see a point to your EnableEvents. I typically use that sort of thing when I want to modify cell value(s) when an intersection range is found.

[Paul_Hossler]

That´s the doubt: I'm not changing the selection!
The code is firing as I hit 'Enter', WITHOUT changing the selected cell.


That is normal Excel behavior, maybe a little strange, but normal.

Maybe the way to think what's happening is

1. Pressing the enter key triggers the event; clicking in the Active Cell does not. :think:

2. The Active Cell is being (re-)Selected and that causes the event to fire. :think:

Is this causing a problem? There might be another way to workaround it


Option Explicit

Dim sOldAddress As String

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Cells(1, 1).Address = sOldAddress Then Exit Sub

sOldAddress = Target.Cells(1, 1).Address
MsgBox Target.Address
End Sub


Paul

[omp001]

That is normal Excel behavior, maybe a little strange, but normal.
Hello, Paul.
Thanks for understanding what I was trying to say ...

Now I know that it's a feature of Excel.

My problem: incidentally, the code I had done started shooting, without changing the selection. Then I found the cause: the user was typing just the 'Enter' key.

Actually, the code you suggested inhibits the firing of the code by pressing 'Enter'. I'll use it to prevent unwanted results.


Perhaps this event could have another name:
Private Sub Worksheet_SelectionChangeOrHitEnter (ByVal Target As Range)

Thanks to all.

[Paul_Hossler]

Perhaps this event could have another name:
Private Sub Worksheet_SelectionChangeOrHitEnter (ByVal Target As Range)


The next time Bill Gates takes me to lunch, I'll suggest that.

But until that happens, you know how to make VBA do what you want it to do

Paul

[Aussiebear]

Be cheaper to come to Australia and I'll buy you lunch