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?

 

[Yongle]

Please post the code for both events in full
- we can help you amend it
- please use code tags
- click on # icon above post window and pase code inside the tags
[ CODE ] code goes here [ /CODE ]

It is NOT a bug
Your code requires amending to prevent ONE event trigerring the other
Trigger is disabled with

Application.EnableEvents = False

and enabled with

Application.EnableEvents = True

But how they are inserted, and where, dependes on the code

It is vital to ensure that the trigger is re-enabled before the end of the macro (regardless of what happens in either macro)
- otherwise ALL events are disabled

 

[tiredofit]

Forgot to mention that EnableEvents was switched off immediately after the Change event was activated.

 

[Yongle]

Our posts crossed - please look at post#2

 

[tiredofit]

Our posts crossed - please look at post#2

OK, here is the code.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    With Application
    
        .EnableEvents = False
        
        Select Case Target.Column
        
            Case 1
            
                Me.Cells(Target.Row, 2).Select
        
            Case 2
            
                Me.Cells(Target.Row, 3).Select
        
            Case 3
            
                Me.Cells(Target.Row, 4).Select
        
            Case 4
            
                Me.Cells(Target.Row, 1).Select
        
        End Select
        
        .EnableEvents = True
        
    End With
    
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    MsgBox "Bug!"
    
End Sub

On sheet1, select cell A1. Obviously it triggers the Worksheet_SelectionChange event, so the message box pops up.

But if you type in a value into cell A1, all I expected to happen is the cursor moving to cell B1.

But what actually happens is the cursor moves to cell B1 AND the message box gets triggered.

Why is that?

BTW, this was tested in Excel 2010.

Thanks

 

[Yongle]

You need to look carefully at both event macros and determine which lines in the one macro trigger the other event to fire

In Selection_Change look for
- lines which amend the values in any cells in the same worksheet
- every value being updated triggers Worksheet_Change

In Worksheet_Change look for
- any lines which select or activate cells in the same worksheet
- each Select/Activate triggers Worksheet_Change

Having determined what is causing the problem then amend the code so that EnableEvents is set to False before the unwanted trigger happens
Without seeing the code, I cannot tell you where the line should go
- but try placing this early in both macros unless any of the triggers are required

Application.EnableEvents = False

Try placing this as the last line in both macros

Application.EnableEvents = True

Important
- you MUST evaluate at possible exit routes out of both macros and make sure that EnableEvents is always set to True

If A = X Then 
   Exit Sub
Else
  Target.Offset(, 3 )  = A
End If

[I][COLOR=#006400]' some other code etc[/COLOR][/I]

End Sub

now becomes

If A = X Then 
   GoTo Handling
Else
  Target.Offset(, 3 )  = A
End If

[I][COLOR=#006400]' some other code etc[/COLOR][/I]

Handling:
Application.EnableEvents = True

End Sub

Something else to reduce triggers is to avoid selecting cells in Worksheet_Change
- cells do not require selecting to be amended

 

[Yongle]

Oh we have crossed again
- will now look at your code...

 

[tiredofit]

You need to look carefully at both event macros and determine which lines in the one macro trigger the other event to fire

In Selection_Change look for
- lines which amend the values in any cells in the same worksheet
- every value being updated triggers Worksheet_Change

In Worksheet_Change look for
- any lines which select or activate cells in the same worksheet
- each Select/Activate triggers Worksheet_Change

Having determined what is causing the problem then amend the code so that EnableEvents is set to False before the unwanted trigger happens
Without seeing the code, I cannot tell you where the line should go
- but try placing this early in both macros unless any of the triggers are required

Application.EnableEvents = False

Try placing this as the last line in both macros

Application.EnableEvents = True

Important
- you MUST evaluate at possible exit routes out of both macros and make sure that EnableEvents is always set to True

If A = X Then 
   Exit Sub
Else
  Target.Offset(, 3 )  = A
End If

[I][COLOR=#006400]' some other code etc[/COLOR][/I]

End Sub

now becomes

If A = X Then 
   GoTo Handling
Else
  Target.Offset(, 3 )  = A
End If

[I][COLOR=#006400]' some other code etc[/COLOR][/I]

Handling:
Application.EnableEvents = True

End Sub

Something else to reduce triggers is to avoid selecting cells in Worksheet_Change
- cells do not require selecting to be amended

I appreciate your spending the time on this.

Our paths have crossed again, please look at post #5 of mine.

Haha

 

[Yongle]

I am busy for the rest of the day, but I think that all that is happening is that your cursor is moving to the next cell triggering the message box BEFORE the DisableEvents

Prove me right or wrong by amending one setting

File \ Options \ Advanced \ After Pressing Enter Move Selection - CLEAR that box and stop the cursor from moving to another cell

The problem should go away

It is not a bug - you have told Excel to "Message Box" every time a different cell is selected and your settings are telling Excel to select a different cell after entering data - so behaviour is as expected. The disable events is happening AFTER the trigger

Next tell me what you want to happen
- you probably do not want that to be the permanent setting
- so we need to amend the code to deal with it or temporartily prevent the next cell being selected
- so I need to know which way you need to go

Will look again tomorrow

 

[tiredofit]

I am busy for the rest of the day, but I think that all that is happening is that your cursor is moving to the next cell triggering the message box BEFORE the DisableEvents

Prove me right or wrong by amending one setting

File \ Options \ Advanced \ After Pressing Enter Move Selection - CLEAR that box and stop the cursor from moving to another cell

The problem should go away

It is not a bug - you have told Excel to "Message Box" every time a different cell is selected and your settings are telling Excel to select a different cell after entering data - so behaviour is as expected. The disable events is happening AFTER the trigger

Next tell me what you want to happen
- you probably do not want that to be the permanent setting
- so we need to amend the code to deal with it or temporartily prevent the next cell being selected
- so I need to know which way you need to go

Will look again tomorrow

I'm afraid I tried your suggestion but the problem still persists.

The point is to "remind" the user to go to the next cell.

They enter something in A1 and the program "reminds" them the next value to enter is B1, then C1, etc.

It seems Excel / VBA "ignores" that EnableEvents is switched off.