Prevent UserForm from displaying

[Patrick C. Simonds]

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

 

[Per Jessen]

Turn off events in your code:

Sub DateSort()
Application.EnableEvents=False

'Your Code

Application.EnableEvents=True
End Sub

Regards,
Per

 

[Patrick Molloy]

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

 

[Peter T]

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