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]

It does not "Ignore" the EnableEvents.
When you change a cell & then press enter or tab, you are selecting a different cell. That triggers the SelectionChange Event as well as the Change Event.

 

[tiredofit]

It does not "Ignore" the EnableEvents.
When you change a cell & then press enter or tab, you are selecting a different cell. That triggers the SelectionChange Event as well as the Change Event.

But in my code, enable events was switched off before the cell was selected, then switched back on afterwards.

 

[Fluff]

It happens before you code even runs. When you physically change a cell & hit enter or tab, that selects a different cell, thus triggering the selectionchange event.

 

[tiredofit]

It happens before you code even runs. When you physically change a cell & hit enter or tab, that selects a different cell, thus triggering the selectionchange event.

If you put a break point on the line:

Worksheet_SelectionChange

Then just select a cell on the worksheet, it will hit the breakpoint. This is normal.

But in my code, in the Worksheet_Change event, enable events is switched off, then a cell is selected, then enable events is switched back on.

The code proceeds to run to End Sub of the Worksheet_Change sub but then jumps to the Worksheet_SelectionChange event. That is not what I expect.

 

[Fluff]

But in my code, in the Worksheet_Change event, enable events is switched off, then a cell is selected, then enable events is switched back on.

NO, NO, NO!!
Did you read this

It happens before you code even runs.

Put these in a sheet module

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

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

Now change any cell on that sheet. What happens?

 

[tiredofit]

NO, NO, NO!!
Did you read this

Put these in a sheet module

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

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

Now change any cell on that sheet. What happens?

As soon as I select a cell, the message box shows Select.

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

 

[tiredofit]

Sorry to confuse.

Dim bNeverMind As Boolean[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Private Sub Worksheet_Change(ByVal Target As Range)[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]  bNeverMind = Application.MoveAfterReturn[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]  MsgBox "Change" ' for demo[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]  ' other code[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]End Sub[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana][/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]Private Sub Worksheet_SelectionChange(ByVal Target As Range)[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]  If bNeverMind Then[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    bNeverMind = False[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]  Else[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    MsgBox "SelectionChange" ' for demo[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]    ' other code[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]  End If[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]End Sub[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]

​

Thanks.

Your code is the solution I am seeking.

BTW, what is this?

 Application.MoveAfterReturn

​

Could I have substituted it with True?


​

 

[shg]

Sorry to confuse.

Dim bNeverMind As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
  bNeverMind = Application.MoveAfterReturn
  MsgBox "Change" ' for demo
  ' other code
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If bNeverMind Then
    bNeverMind = False
  Else
    MsgBox "SelectionChange" ' for demo
    ' other code
  End If
End Sub

 

[Yongle]

@tiredofit - you aready have your solution, but I do not think you understand what is is happening

Add a NEW sheet and put this code in the sheet module of that sheet

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

Select A5 and amend its value

But before doing that write down the number and sequence of message boxes you expect to see
ie write down something like B A A or A B B A or whatever you expect and then compare the result
The code takes you to C5.
Predict what happens before amending its value

 

[tiredofit]

@tiredofit - you aready have your solution, but I do not think you understand what is is happening

Add a NEW sheet and put this code in the sheet module of that sheet

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

Select A5 and amend its value

But before doing that write down the number and sequence of message boxes you expect to see
ie write down something like B A A or A B B A or whatever you expect and then compare the result
The code takes you to C5.
Predict what happens before amending its value

I understand your code and it is behaving as expected.

The moment this line has executed:

Target.Offset(, 2).Select

it immediately jumps to the Worksheet_SelectionChange sub. I expect this.

If I amended you code to this:

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

then after this line has been executed:

Target.Offset(, 2).Select

it does NOT jump to the Worksheet_SelectionChange sub. Instead goes to the next line, ie:

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

​

This again is what I expected.

What I did not expect is that when the code has finished running this line:

Application.EnableEvents = True

it goes to the End Sub (I expect that) but then jumps to this line:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

That's what I don't get.

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.

_{^{<strike>
</strike>}}​

If I had this situation:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Range("A1").Value = 1
End Sub

and I changed a value on the sheet, it would be in an infinite loop.

To overcome that, I would write:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Range("A1").Value = 1
    Application.EnableEvents = True
End Sub

when the code executes this line:

Range("A1").Value = 1

​

because EnableEvents = False, it goes to this line:

Applicaition.EnableEvents = True

then goes to End Sub and stops.

It DOES NOT go back into the start of the sub, so doesn't "remember" an event took place because EnableEvents was set to False. This does makes sense to me.



​

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