Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable selective events
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable selective events
Use a global boolean, set that when you want and react according to that
boolean. -- __________________________________ HTH Bob "Patrick C. Simonds" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable selective events
I have to admit I have no idea of how to do that.
"Bob Phillips" wrote in message ... Use a global boolean, set that when you want and react according to that boolean. -- __________________________________ HTH Bob "Patrick C. Simonds" wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Disable selective events
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" wrote in message ... I have to admit I have no idea of how to do that. "Bob Phillips" wrote in message ... Use a global boolean, set that when you want and react according to that boolean. -- __________________________________ HTH Bob "Patrick C. Simonds" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Events won't Disable! | Excel Discussion (Misc queries) | |||
Disable events | Excel Programming | |||
disable events does not work | Excel Programming | |||
Disable Events | Excel Programming | |||
Disable Key Events | Excel Programming |