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

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

--
__________________________________
HTH

Bob

"Patrick C. Simonds" <(E-Mail Removed)> wrote in message
news:e$(E-Mail Removed)...
> 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

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

--
jb


"Patrick C. Simonds" wrote:

> 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
>
>

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



"Bob Phillips" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> Use a global boolean, set that when you want and react according to that
> boolean.
>
> --
> __________________________________
> HTH
>
> Bob
>
> "Patrick C. Simonds" <(E-Mail Removed)> wrote in message
> news:e$(E-Mail Removed)...
>> 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
>
>

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.

--
__________________________________
HTH

Bob

"Patrick C. Simonds" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>I have to admit I have no idea of how to do that.
>
>
>
> "Bob Phillips" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> Use a global boolean, set that when you want and react according to that
>> boolean.
>>
>> --
>> __________________________________
>> HTH
>>
>> Bob
>>
>> "Patrick C. Simonds" <(E-Mail Removed)> wrote in message
>> news:e$(E-Mail Removed)...
>>> 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
>>
>>
>