suspend a routine
 

The code below calls a userform any time a cell is selected within that
range and it works fine, but, the routine which is triggered by a button on
the userform also selects a cell within that range causing the userform to
reappear. Is there any way to suspend that code while the macro runs its
course?


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If Not Application.Intersect(Target, Range("A5:A200")) Is Nothing Then
Path_Of_Action.Show

End Sub

suspend a routine
 

in your userform code:

Application.EnableEvents = False
'your selection line here
Application.EnableEvents = True

In article ,
"Patrick Simonds" wrote:

The code below calls a userform any time a cell is selected within that
range and it works fine, but, the routine which is triggered by a button on
the userform also selects a cell within that range causing the userform to
reappear. Is there any way to suspend that code while the macro runs its
course?


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If Not Application.Intersect(Target, Range("A5:A200")) Is Nothing Then
Path_Of_Action.Show

End Sub

suspend a routine
 

You could probably remove the select statement from your code as selects are
rarely required. That beind said you can suspend the events from firing if
you want something like this (always use an error handler when you toggle
application level settings)...

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
on error goto errorhandler
application.enableevents = false 'turn off events
If Not Application.Intersect(Target, Range("A5:A200")) Is Nothing Then
Path_Of_Action.Show

ErrorHandler:
application.enableevents = true 'turn events back on...
End Sub

--
HTH...

Jim Thomlinson


"Patrick Simonds" wrote:

The code below calls a userform any time a cell is selected within that
range and it works fine, but, the routine which is triggered by a button on
the userform also selects a cell within that range causing the userform to
reappear. Is there any way to suspend that code while the macro runs its
course?


Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If Not Application.Intersect(Target, Range("A5:A200")) Is Nothing Then
Path_Of_Action.Show

End Sub