Exiting Worksheet_Change from a sub routine

[MrTeeny]

Is it possible to exit a Worksheet_change event from a sub routine? So say I Call a sub routine that's triggered within my worksheet_change event can I exit simply from the sub routine or would the code have to go back and finish whatever coding was left after my Calling the sub. Hopefully that's not too garbled to understand.

Thanks

 

[My Aswer Is This]

You can do it like this:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A:A")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
    If Target.Value = "Fred" Then
        Call HelloGeorge
        Exit Sub
    Else
        Cells(1, 5).Value = Target.Value
    End If
    End If
End Sub

 

[MrTeeny]

Thanks for the reply, I was more looking to see if I could exit the whole worksheet routine from the HelloGeorge sub routine if certain conditions were met withing that HelloGeorge sub routine, then it woudn't have to go thru the rest of any code in the worksheet_change event.

 

[MrTeeny]

So the HelloGeorge routine would be run regardless, just stuck as a rouinte within a module to stop the worksheet_change coding from getting too overcrowded and making things easier to edit and amend

 

[My Aswer Is This]

I see. Not sure how you can do that.

 

[MrTeeny]

I was hoping there would be an easy way, only other options I could think of were maybe seeing if I could pass a variable back to the worksheet_change routine or even set a global variable to be checked when the code resumes after the sub routine has completed and a simple If can exit the sub from there. Just hoped they'd be a neater and easier way to exit the worksheet_change

btw how do you edit the posts? I couldn't see an icon, maybe it's cos I haven't done enough posts?

 

[NoSparks]

In the HelloGeorge sub, use of the END instruction will terminate things immediately, but you'll want to read the help files for possible side affects.

 

[My Aswer Is This]

I was hoping there would be an easy way, only other options I could think of were maybe seeing if I could pass a variable back to the worksheet_change routine or even set a global variable to be checked when the code resumes after the sub routine has completed and a simple If can exit the sub from there. Just hoped they'd be a neater and easier way to exit the worksheet_change

btw how do you edit the posts? I couldn't see an icon, maybe it's cos I haven't done enough posts?

You can only edit post within 10 minutes of the time you make the post. After 10 minutes you cannot edit the post.

 

[MrTeeny]

In the HelloGeorge sub, use of the END instruction will terminate things immediately, but you'll want to read the help files for possible side affects.

Thanks, END seems a bit drastic looking at the help files, I'll declare a Public variable then have the HelloGeorge set it if needed and have an If statement to exit the worksheet_change routine when it resumes for now.

 

[Jaafar Tribak]

short of using a Global variable, you could pass a boolean flag argument ByRef to the HelloGeorge Callee routine.. This flag could then be set in the Callee routine if the condition is met and passed back to the Caller

Something like this :

Private Sub Worksheet_Change(ByVal Target As Range)   
     
        [COLOR=#008000]'other code goes here..[/COLOR]
        
        Dim bExit As Boolean
        
        Call HelloGeorge(bExit)

        If bExit Then Exit Sub
        
        [COLOR=#008000]'other code goes here..[/COLOR]

End Sub


Sub HelloGeorge(ByRef bExit As Boolean)

   [COLOR=#008000] 'other code goes here..[/COLOR]
    
    [B][COLOR=#008000]'if condition is met set the boolean flag[/COLOR][/B]
    bExit = True
    
  [COLOR=#008000]  'other code goes here..[/COLOR]
    
End Sub