Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent UserForm from displaying
The code below causes UserForm2 to display when I select a cell within the
range. Is there any way in the datesort module to prevent UserForm2 from displaying? Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If Not Application.Intersect(Target, Range("C5:V700")) Is Nothing Then UserForm2.Show End Sub ----------------------------- Sub datesort() ' ' datesort Macro ' Macro recorded 3/23/2004 by mary ' ' Module10.UnProtectSheet Rows("7:1000").Select Selection.Sort Key1:=Range("F7"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortTextAsNumbers Range("G7").Select Module10.ProtectSheet End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent UserForm from displaying
Turn off events in your code:
Sub DateSort() Application.EnableEvents=False 'Your Code Application.EnableEvents=True End Sub Regards, Per "Patrick C. Simonds" skrev i meddelelsen ... The code below causes UserForm2 to display when I select a cell within the range. Is there any way in the datesort module to prevent UserForm2 from displaying? Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If Not Application.Intersect(Target, Range("C5:V700")) Is Nothing Then UserForm2.Show End Sub ----------------------------- Sub datesort() ' ' datesort Macro ' Macro recorded 3/23/2004 by mary ' ' Module10.UnProtectSheet Rows("7:1000").Select Selection.Sort Key1:=Range("F7"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortTextAsNumbers Range("G7").Select Module10.ProtectSheet End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent UserForm from displaying
when running code, its very rare that the ranges affected need to be
selected. Certainly switching off the events as suggested is one way, but changing the code to my mind is "better" since its faster and prevents the selection change event firing - unless you need the specific cell selected? Sub datesort() ' ' datesort Macro ' Macro recorded 3/23/2004 by mary ' ' Module10.UnProtectSheet Rows("7:1000").Sort Key1:=Range("F7"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortTextAsNumbers Module10.ProtectSheet ' delete the next three if not required application.enableevents = false Range("G7").Select application.enableevents = true End Sub "Patrick C. Simonds" wrote in message ... The code below causes UserForm2 to display when I select a cell within the range. Is there any way in the datesort module to prevent UserForm2 from displaying? Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If Not Application.Intersect(Target, Range("C5:V700")) Is Nothing Then UserForm2.Show End Sub ----------------------------- Sub datesort() ' ' datesort Macro ' Macro recorded 3/23/2004 by mary ' ' Module10.UnProtectSheet Rows("7:1000").Select Selection.Sort Key1:=Range("F7"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortTextAsNumbers Range("G7").Select Module10.ProtectSheet End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Prevent UserForm from displaying
Either you want the event to run or not. If you have written the event stub
when the event is triggered your event code will run. If the event will be called by your myCell.Select in another procedure, as suggested, temporarily disable events. Ensure there is an error handler such that events will definitely get re-enabled. Another approach, and possibly a safer one, is to do something like this - Public gbExit as boolean ' in a normal module sub MyProc() on error goto errH gbExit = True 'code done: gbExit = false Exit Sub errH: resume done end sub in the event code If gbExit then Exit sub Regards, Peter T "Patrick Molloy" wrote in message ... when running code, its very rare that the ranges affected need to be selected. Certainly switching off the events as suggested is one way, but changing the code to my mind is "better" since its faster and prevents the selection change event firing - unless you need the specific cell selected? Sub datesort() ' ' datesort Macro ' Macro recorded 3/23/2004 by mary ' ' Module10.UnProtectSheet Rows("7:1000").Sort Key1:=Range("F7"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortTextAsNumbers Module10.ProtectSheet ' delete the next three if not required application.enableevents = false Range("G7").Select application.enableevents = true End Sub "Patrick C. Simonds" wrote in message ... The code below causes UserForm2 to display when I select a cell within the range. Is there any way in the datesort module to prevent UserForm2 from displaying? Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range) If Not Application.Intersect(Target, Range("C5:V700")) Is Nothing Then UserForm2.Show End Sub ----------------------------- Sub datesort() ' ' datesort Macro ' Macro recorded 3/23/2004 by mary ' ' Module10.UnProtectSheet Rows("7:1000").Select Selection.Sort Key1:=Range("F7"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortTextAsNumbers Range("G7").Select Module10.ProtectSheet End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UserForm not displaying | Excel Programming | |||
Prevent closing userform | Excel Programming | |||
Prevent closing userform | Excel Programming | |||
How to prevent a formula from displaying ? | Excel Discussion (Misc queries) | |||
HOW DO I PREVENT HIDDEN CELLS FROM DISPLAYING WHEN I COPY | Excel Discussion (Misc queries) |