Disable selective events

[Patrick C. Simonds]

In the code below I use Application.EnableEvents = False to prevent the
showing of UserForm1 while the code is running. The problem is that it
prevents all events from happening. Is there any way to prevent UserForm1
from showing while still allowing other events to happen?



Private Sub CommandButton1_Click()
Unload End_Of_Month_Disposal
Application.EnableEvents = False
Application.ScreenUpdating = False

ActiveWorkbook.Worksheets("2009 Training").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("2009 Training").Sort.SortFields.Add
Key:=Range( _
"A3:A5000"), SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("2009 Training").Sort
.SetRange Range("A2:N5000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Range("B2").Select

Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub

 

[Bob Phillips]

Use a global boolean, set that when you want and react according to that
boolean.

 

[john]

as an idea, what about placing the unload End_Of_Month_Disposal statement at
the end of your procedure and then add at start of each event you want to be
ignored
the following line:

If End_Of_Month_Disposal.Visible = True Then Exit Sub

not tested but something along those lines may be worth a try.

Private Sub CommandButton1_Click()

With Application
'.EnableEvents = False
.ScreenUpdating = False
End With

With ActiveWorkbook.Worksheets("2009 Training")
.Sort.SortFields.Clear
.Sort.SortFields.Add Key:=Range("A3:A5000"), _
SortOn:=xlSortOnValues, _
Order:=xlAscending, DataOption:=xlSortNormal

With .Sort
.SetRange Range("A2:N5000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

.Range("B2").Select
End With

With Application
'.EnableEvents = True
.ScreenUpdating = True
End With

Unload End_Of_Month_Disposal

End Sub

 

[Patrick C. Simonds]

I have to admit I have no idea of how to do that.

 

[Bob Phillips]

Create a global variable in a standard module

Global fSuppressEvents As Boolean

and then in your code test it before showing the form say

If Not fSuppressEvents Then Show Userform1

and then set/reset that flag in you main code

'do something
Set fSuppressEvents =True
'do some more
fSuppressEvents = False

'etc.