Need Help Understanding Events

[Opv]

If I implement Application.EnableEvents = False at the beginning of the Worksheet_Change sub and Application.EnableEvents = True at the end of the sub, and then I proceed to change the value of a cell within that particular worksheet, should a Worksheet_SelectionChange event be triggered once Events are turned back on to account for the fact that the cursor position moved after the Target value was changed?

For example, if I create a new blank workbook and paste the following code in one of the Sheet modules, a SelectionChange event seems to get called every time, regardless whether Events are turned off, and regardless of whether I create a global Boolean variable and set it accordingly.

Option Explicit
Public testing As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False: testing = True


'Change the value of a cell in the worksheet


Application.EnableEvents = True: testing = False
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If testing = True Then Exit Sub
MsgBox "selection changed"


End Sub

Could someone please explain whether I'm trying to do the impossible?

P.S. I'm running Excel 2007.

[mdmackillop]

Try

Option Explicit
Public testing As Boolean
 
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False: testing = True
     'Change the value of a cell in the worksheet
End Sub
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = True: testing = True
    If testing = True Then MsgBox "selection changed"
End Sub

[Opv]

Try

Option Explicit
Public testing As Boolean
 
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False: testing = True
     'Change the value of a cell in the worksheet
End Sub
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = True: testing = True
    If testing = True Then MsgBox "selection changed"
End Sub

Thanks! Just out of curiosity, why does "If testing = true then Exit Sub" not accomplish the same thing? I was under the understanding that such a line would exit the sub before any subsequent line of code had a chance to be executed?

[mdmackillop]

Nothing happens after the Exit Sub except the MsgBox. I removed it to see if the macro was triggered.

[Opv]

Nothing happens after the Exit Sub except the MsgBox. I removed it to see if the macro was triggered.

I am misunderstanding you. Are you saying that after Exit Sub, the Msgbox popup is NOT running or that it is running? For me, after the Exit Sub statement, any and all of the following lines of code seem to be running. What I am wanting to accomplish is to ignore all code following the Exit Sub statement if either Events are turned off or the "testing" boolean statement is = true. I can't seem to accomplish that for some reason.

[Paul_Hossler]

I would not think of events that way

Say for example, if you changed B4 then you also want to change C4 to be 2x that

If you only had this ...

Option Explicit 

Private Sub Worksheet_Change(ByVal Target As Range) 

If Target.Address = "$B$4" then
    Range("C4").Value = 2 * Range("B4").value
Endif

End Sub

... when you change the value in B4, the event will fire, BUT it will fire again because the event handler changed C4

Turning off event handling will allow C4 to be changed and not trigger a circular event chain

Option Explicit 

Private Sub Worksheet_Change(ByVal Target As Range) 

If Target.Address = "$B$4" then
    Application.EnableEvents = False
    Range("C4").Value = 2 * Range("B4").value
    Application.EnableEvents = True
Endif

End Sub

This fires when the selection changes

Option Explicit 
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    MsgBox "selection changed to " & Target.Address
End Sub

[Opv]

Thanks for the help. I realize that my initial example is overly broad. That said, are there no instances in which a complete disabling of the SelectionChange sub might be appropriate?

[mdmackillop]

I can only recall one use I had for SelectionChange; preventing users from accessing Rows 1:4 to change things. I can't see a reason to consider it in conjunction with another macro. It can obviously be prevented from running after another macro by leaving the focus on the Selection area of that macro.

[Opv]

Thanks for the help.

[Opv]

I think figured out at least part of what I initially found so confusing, and why part of my initial reasoning resulted in such a broad approach. What I was really curious about is if and to what extent events triggered in WorksheetChange effect events triggered in the SelectionChange sub. What I really needed to understand is how to capture the previously edited range so that I could trigger (or not trigger) a particular SelectionChange event. Once I capture the previously edited range and configure SelectionChange to trigger/not trigger accordingly, everything seems to work as desired. Thanks for helping me think through this process.