Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoiding SELECT
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoiding SELECT
Hi Jim,
try to sort a range without selecting it (hey, sounds like your question ;). Eg: Sub Makro2() Set myRange = Range("B2:D13") myRange.Sort Key1:=Range("B3"), Order1:=xlAscending, Header:=xlGuess, _ OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal End Sub regards arno |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Avoiding SELECT
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Avoiding #N/A | Excel Discussion (Misc queries) | |||
Avoiding #N/A | Excel Discussion (Misc queries) | |||
Avoiding #value | Excel Worksheet Functions | |||
Avoiding #NUM! | Excel Worksheet Functions | |||
vlookup avoiding #N/A | Excel Worksheet Functions |