PDA

View Full Version : using a macro and time



Tenspeed39355
05-18-2006, 03:48 PM
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
05-18-2006, 04:31 PM
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
05-18-2006, 06:36 PM
Since I am unsure of what to do could you please write in the on time and loop. Thanks

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

geekgirlau
05-18-2006, 07:31 PM
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.

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

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

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

Bob Phillips
05-19-2006, 03:10 AM
I think that should be



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




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



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
05-22-2006, 02:32 AM
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
05-22-2006, 12:51 PM
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.