Disable selective events
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
|