VBA Worksheet_Chnage event bug

[tiredofit]

My program has both a Worksheet_Change event and a Worksheet_SelectionChange event.

For some reason, when it runs to the end of the Worksheet_Change event, it jumps to the Worksheet_SelectionChange event.

Has anyone experienced this before?

 

[Fluff]

Then when I change a value, it shows Change. After clicking OK, it shows Select.

Yet there is nothing in either Event that selects a cell, but it still runs both codes.
That's because when you change a cell & hit Enter or Tab, a new cell is selected. Thereby triggering both events.
It has absolutely nothing to do with the code inside the Event.

 

[shg]

It is as if it "remembers" an event is due to take place, waits for Application.EnableEvents = True then kicks off the Worksheet_SelectionChange event.

It's not that.

Both events are queued, one behind the other, before you've disabled events. And disabling events doesn't flush the queue, it prevents new events from queuing.

 

[tiredofit]

It's not that.

Both events are queued, one behind the other, before you've disabled events. And disabling events doesn't flush the queue, it prevents new events from queuing.

So are you saying when you turn EE back on, the events return? If so, why does my second example (the one with only the Worksheet_Change event) not be stuck on an infinite loop the moment EE is turned back on?

 

[tiredofit]

Yet there is nothing in either Event that selects a cell, but it still runs both codes.
That's because when you change a cell & hit Enter or Tab, a new cell is selected. Thereby triggering both events.
It has absolutely nothing to do with the code inside the Event.

in this code by yongle:

Option Explicit
Dim c As Integer
Private Sub Worksheet_Change(ByVal Target As Range)
    c = c + 1
    Target.Offset(, 2).Select
    MsgBox "A" & vbCr & "Change " & c
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    c = c + 1
    MsgBox "B" & vbCr & "SelectionChange " & c
End Sub

I change a value in a cell, the code starts off here:

Private Sub Worksheet_Change(ByVal Target As Range)

then runs to here:

<strike></strike>Target.Offset(, 2).Select

as soon as that line is executed, it jumps to the Worksheet_SelectionChange event, runs to the end of that sub, then returns to the Worksheet_Cahnge event line:

MsgBox "A" & vbCr & "Change " & c

runs to End Sub. That makes sense.

but then it jumps to the first line of Worksheet_SelectionChange event. But nothing's selected or changed by this stage, so why is it jumping there?



<strike></strike><strike></strike><strike></strike>

 

[Fluff]

In post#16 you said

Then when I change a value, it shows Change. After clicking OK, it shows Select.

referring to these two codes

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

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

Neither of them change the selected cell, yet both message boxes appear, meaning both events have been triggered.

 

[tiredofit]

In post#16 you saidreferring to these two codes

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

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

Neither of them change the selected cell, yet both message boxes appear, meaning both events have been triggered.

As I said in #16, I changed a value in a cell, it kicked off the Worksheet_Change event, then after that whole sub ran, it went to the Worksheet_SelectionChange event.

 

[Fluff]

The first time it goes to the selectionchange event, is because the change event has selected another cell.
The 2nd time is for the same reason that the selectionchange event triggers with the code i posted.

 

[tiredofit]

Many thanks to all.

I'll use shg's solution for my needs.

 

[shg]

Application.MoveAfterReturn[/code

Could I have substituted it with True?[/quote]
If MoveAfterReturn is True, you want to ignore the associated SelectionChange event. If it's not, you don't.

 

[tiredofit]

If MoveAfterReturn is True, you want to ignore the associated SelectionChange event. If it's not, you don't.

Thanks again.