looking for a BIT OF HELP HERE... I NEED TO AUTO SORT A LIST OF CELLS EVERY MINUTE OR SO... HOW DO I DO IT???? THANKS IN ADVANCE
You need to run a timed macro. You can easily create the macro using the macro recorder. The timer is a bit more trouble to program, but not brain surgery.
record the sorting macro from Tools-macro-record open the macro's vba module: Tools-macro-edit at the end of the macro's vba code, before End Sub, place this statement: Application.OnTime (Now + TimeValue("00:01:00")) , "[insert macro's name here]" good luck. p.s. if this works ok, you may need a bit more code to clean up / stop running the macro when you close the worksheet.
Here is a complete sample of the windows Declare function for the VBA Timer event and the VBA code itself in three parts... Declares Public Declare Function SetTimer Lib "user32" ( _ ByVal HWnd As Long, ByVal nIDEvent As Long, _ ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long Public Declare Function KillTimer Lib "user32" ( _ ByVal HWnd As Long, ByVal nIDEvent As Long) As Long VBA Timer Code... Sub StartTimer () On Error GoTo Err_Prob TimerSeconds = 2 'pop the windows time every x seconds TimerID = SetTimer(0&, 0&, TimerSeconds * 1000&, AddressOf TimerProc) Exit_Err_Prob: Exit Sub Err_Prob: MsgBox Err.Number & " " & Err.Description Resume Exit_Err_Prob End Sub Sub EndTimer() On Error GoTo Err_Prob runTimer = False KillTimer 0&, TimerID Exit_Err_Prob: Exit Sub Err_Prob: MsgBox Err.Number & " " & Err.Description Resume Exit_Err_Prob End Sub Sub TimerProc(ByVal HWnd As Long, ByVal uMsg As Long, ByVal nIDEvent As Long, ByVal dwTimer As Long) On Error GoTo Err_Prob DO YOUR CODE HERE TO SORT THE CELLS / RANGES YOU WANT TO.. 'you also need error handling since pulling and pushing data burps once in awhile... Exit_Err_Prob: Exit Sub Err_Prob: If Err.Number = 55 Then 'take some code action here... Resume Next Else Err.Clear ' Clear Err object fields Resume Next MsgBox Err.Description End If If Err.Number = 440 Or Err.Number = 432 Then 'Tell user what happened. Then clear the Err object. MsgBox "There was an error attempting to open the Automation object!" Err.Clear ' Clear Err object fields Resume Next End If EndTimer StartTimer Debug.Print Err.Number & " " & Err.Description Resume Exit_Err_Prob End Sub You build a macro button on the front interface of Excel that calls the start timer event and a button that does the End Timer event and -- bada bing... you got a timing system... htHelps... cj... ______________________ HAVE STOP - WILL TRADE If You Have The Vision We Have The Code
OR... Keep two separate worksheets within the same workbook. Use one worksheet as the primary data worksheet and use the RANK() function to identify how your data is to be sorted via numbering (ie. 1, 2, 3, ..., N). THEN On the second worksheet, choose a column and number the cells 1...N. Use vlookup of the numbered column of this second worksheet to index the RANK() column of the first worksheet. This will sort your data in realtime continually. I have an example workbook somewhere but it is on a different machine... It does not require any macro programming...