Avoiding SELECT

[JimP]

To All,

I have a Modeless form with a control button on Sheet(1) ... When I
select a specific button on the Modeless form, it performs a number of
steps, 1 of which is to sort data on a different sheet ... Because my
code makes a selection on the other sheet in order to do the sort,
when I eventually return to Sheet(1) the Modeless form had already
been closed. It closed because of my protective code in
Worksheet_Deactivate() ... which currently PREVENTS eXcel from
ABORTING when the NonModal forms are hidden using Me.Hide ... This
took me days to figure out!

GOAL: is to be able to launch from the NonModal form on Sheet(1) a
task that sorts data on Sheet(2) without the Worksheet_Deactivate()
executing on Sheet(1).

'The following "Deactivate" code prevents ABORTS, and is info only
'I have successfully used Me.Hide BUT ...
' eXcel ABORTS if user deletes the sheet that has the hidden NonModal
form

Private Sub Worksheet_Deactivate()
' If FORM is being displayed as NonModal, remove it
' eXcel aborts if NonModal is hidden with Me.hide AND
' someone then DELETES the worksheet!!!
Application.ScreenUpdating = False
On Error Resume Next
Unload frmNAVIGATION: Application.ScreenUpdating = False
Unload frmOPTIONS: Application.ScreenUpdating = False
Unload frmPrint: Application.ScreenUpdating = False
On Error GoTo 0
End Sub

QUESTION:

How do I re-write the following sort routine to NOT SELECT(?) and
still be able to sort data.

Function OT_INITHOUR_sort()
Range("OT_DATA").Select
Selection.Sort Key1:=Range("OT_INITIAL_HOURS_Cell1"),
Order1:=xlAscending, _
Key2:=Range("OT_SENIORITY_Cell1"), Order2:=xlAscending, _
Header:=xlNo, OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom
End Function

Any Help would be greatly appreciated ...
Thanks,

Jim Pellechi

 

[arno]

Hi Jim,

try to sort a range without selecting it (hey, sounds like your
question . Eg:

Sub Makro2()
Set myRange = Range("B213")
myRange.Sort Key1:=Range("B3"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub


regards

arno

 

[microsoft.public.excel.programming]

Arno,

I've been blind to this technique for so long ... and I have to say
THANKS for pointing it out ... It took Non-Modal forms causing eXcel to
crash before the technique of NOT SELECTING finally sunk in ...

J.Pellechi

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!