using a macro and time

[Tenspeed39355]

I have a macro set up to sort a column. I am using the Ctrl a to start the macro. Is there a way for the macro to sort the column at a set time like every 10 minutes or so. Can I add your response to the macro I have?
Thanks for your time with this
Max

[mdmackillop]

Hi Tenspeed,
Check out the "OnTime method" in Help. I think a loop with this code should solve your problem. If you need further assistance please post your code.
Regards
MD

[Tenspeed39355]

Since I am unsure of what to do could you please write in the on time and loop. Thanks
[VBA]
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 05/18/2006 by
'
' Keyboard Shortcut: Ctrl+a
'
Rows("4:4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("AD4"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub
[/VBA]

[geekgirlau]

I've added a quick check in your macro, just in case another sheet or workbook is active. You need to change "MySheet" to the actual name of your sheet.

[vba]Sub SortMe()
' Keyboard Shortcut: Ctrl+a
'
' check the sheet name as you might have a different sheet active
If ActiveSheet.Name = "MySheet" Then
Rows("4:4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("AD4"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If

' reschedule the "SortMe" macro to run every 10 minutes
Application.OnTime EarliestTime:=Now() + TimeValue("00:10:00"), _
Procedure:="SortMe"
End Sub[/vba]

You also need to add these procedures to the "ThisWorkbook" project:

[vba]Private Sub Workbook_Open()
' schedule the "SortMe" macro to run every 10 minutes
Application.OnTime EarliestTime:=Now() + TimeValue("00:10:00"), _
Procedure:="SortMe"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
' cancel the "SortMe" schedule
Application.OnTime EarliestTime:=TimeValue("17:00:00"), _
Procedure:="SortMe", Schedule:=False
End Sub[/vba]

[Bob Phillips]

I think that should be

[vba]

Public nTime As Double

Sub SortMe()
' Keyboard Shortcut: Ctrl+a
'
' check the sheet name as you might have a different sheet active
If ActiveSheet.Name = "MySheet" Then
Rows("4:4").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Sort Key1:=Range("AD4"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End If

' reschedule the "SortMe" macro to run every 10 minutes
nTime = Now() + TimeValue("00:10:00")
Application.OnTime EarliestTime:=nTime, Procedure:="SortMe"
End Sub
[/vba]

[vba]

Private Sub Workbook_Open()
' schedule the "SortMe" macro to run every 10 minutes
nTime = Now() + TimeValue("00:10:00")
Application.OnTime EarliestTime:=nTime, Procedure:="SortMe"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
' cancel the "SortMe" schedule
Application.OnTime EarliestTime:=nTime, Procedure:="SortMe", Schedule:=False
End Sub

[/vba]

because when you stop the OnTime you must pass the exact scheduled time to the method, and you cannot be sure that when the workbook closes that the scheduled time is 17:00:00.

[geekgirlau]

Point taken, although by using "False" you are basically turning the OnTime function "Off", so my take would be that it doesn't matter what time this is set to ...

[Bob Phillips]

I don't think that you are understanding how OnTime works. If you just stick any old time in the stop statement, it (probably) won't stop anything. You have to specifically state which scheduled procedure you want to stop, which means passing the procedure name, and its NEXT scheduled time.

Try it and see.