Schedule a macro with user defined settings
 

I would like to run a macro, "Perform_Tasks", on a scheduled basis. I would
like users to be able to select certain criteria for this macro, as such:

A B C....
1 Enable/Disable: Enabled
2 Interval: 15
3 Begin Time: 07:00
4 End Time: 13:00

B1:B4 would be user changable values and would dictate how often the macro
kicks off automatically, if enabled. (B1 is a toggle switch between Enable or
Disabled).

Any suggestions?

Matt

Schedule a macro with user defined settings
 

Matt,

I've got a workable version here.

Place this in a module:

Public nexttime As Double
Public interval As Double
Public start As Date
Public endtm As Date
Public Const RunWhat = "Perform_Tasks"
Sub scheduler()
interval = TimeSerial(0, 0, Sheet1.Range("B2"))
start = Sheet1.Range("B3")
endtm = Sheet1.Range("B4")
nexttime = Now + interval
If Sheet1.Range("B1") = "Enabled" Then
If Time = start And Time + interval < endtm Then
Application.OnTime nexttime, RunWhat
Else: Application.OnTime start, RunWhat
End If
End If
End Sub
Sub endscheduler()
On Error Resume Next
Application.OnTime lasttime, RunWhat, , False
Application.OnTime nexttime, RunWhat, , False
End Sub
Sub Perform_Tasks()
lasttime = nexttime
scheduler
Sheet1.Cells(2, 4) = Now
End Sub


And put this is the ThisWorkbook module.

Private Sub Workbook_Open()
scheduler
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
endscheduler
End Sub


If you've already got a Workbook_Open or Workbook_BeforeClose event just add
the lines to those. The interval in B2 is in minutes and B3/B4 should be
times not dates. I'm wasn't sure how you wanted the macro to start so I put
it in the workbook_open procedure. You could also put a call to it from a
Worksheet_Change event for cell B1.

If you've got any questions I'd be glad to assist.

Mike



"Matt" wrote:

I would like to run a macro, "Perform_Tasks", on a scheduled basis. I would
like users to be able to select certain criteria for this macro, as such:

A B C....
1 Enable/Disable: Enabled
2 Interval: 15
3 Begin Time: 07:00
4 End Time: 13:00

B1:B4 would be user changable values and would dictate how often the macro
kicks off automatically, if enabled. (B1 is a toggle switch between Enable or
Disabled).

Any suggestions?

Matt

Schedule a macro with user defined settings
 

Matt,

You can remove the Sheet1.cells(2,4) = now statement toward the bottom.
That's what I was using to know the procedure was being called properly.

Just make sure that the other two lines in "Preform_Tasks" are the last two
lines in your "Perform_Tasks" macro.

Mike

"crazybass2" wrote:

Matt,

I've got a workable version here.

Place this in a module:

Public nexttime As Double
Public interval As Double
Public start As Date
Public endtm As Date
Public Const RunWhat = "Perform_Tasks"
Sub scheduler()
interval = TimeSerial(0, 0, Sheet1.Range("B2"))
start = Sheet1.Range("B3")
endtm = Sheet1.Range("B4")
nexttime = Now + interval
If Sheet1.Range("B1") = "Enabled" Then
If Time = start And Time + interval < endtm Then
Application.OnTime nexttime, RunWhat
Else: Application.OnTime start, RunWhat
End If
End If
End Sub
Sub endscheduler()
On Error Resume Next
Application.OnTime lasttime, RunWhat, , False
Application.OnTime nexttime, RunWhat, , False
End Sub
Sub Perform_Tasks()
lasttime = nexttime
scheduler
Sheet1.Cells(2, 4) = Now
End Sub


And put this is the ThisWorkbook module.

Private Sub Workbook_Open()
scheduler
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
endscheduler
End Sub


If you've already got a Workbook_Open or Workbook_BeforeClose event just add
the lines to those. The interval in B2 is in minutes and B3/B4 should be
times not dates. I'm wasn't sure how you wanted the macro to start so I put
it in the workbook_open procedure. You could also put a call to it from a
Worksheet_Change event for cell B1.

If you've got any questions I'd be glad to assist.

Mike



"Matt" wrote:

I would like to run a macro, "Perform_Tasks", on a scheduled basis. I would
like users to be able to select certain criteria for this macro, as such:

A B C....
1 Enable/Disable: Enabled
2 Interval: 15
3 Begin Time: 07:00
4 End Time: 13:00

B1:B4 would be user changable values and would dictate how often the macro
kicks off automatically, if enabled. (B1 is a toggle switch between Enable or
Disabled).

Any suggestions?

Matt

Schedule a macro with user defined settings
 

Thanks Mike..I'll let you know if I have any questions.

Matt


"crazybass2" wrote:

Matt,

You can remove the Sheet1.cells(2,4) = now statement toward the bottom.
That's what I was using to know the procedure was being called properly.

Just make sure that the other two lines in "Preform_Tasks" are the last two
lines in your "Perform_Tasks" macro.

Mike

"crazybass2" wrote:

Matt,

I've got a workable version here.

Place this in a module:

Public nexttime As Double
Public interval As Double
Public start As Date
Public endtm As Date
Public Const RunWhat = "Perform_Tasks"
Sub scheduler()
interval = TimeSerial(0, 0, Sheet1.Range("B2"))
start = Sheet1.Range("B3")
endtm = Sheet1.Range("B4")
nexttime = Now + interval
If Sheet1.Range("B1") = "Enabled" Then
If Time = start And Time + interval < endtm Then
Application.OnTime nexttime, RunWhat
Else: Application.OnTime start, RunWhat
End If
End If
End Sub
Sub endscheduler()
On Error Resume Next
Application.OnTime lasttime, RunWhat, , False
Application.OnTime nexttime, RunWhat, , False
End Sub
Sub Perform_Tasks()
lasttime = nexttime
scheduler
Sheet1.Cells(2, 4) = Now
End Sub


And put this is the ThisWorkbook module.

Private Sub Workbook_Open()
scheduler
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
endscheduler
End Sub


If you've already got a Workbook_Open or Workbook_BeforeClose event just add
the lines to those. The interval in B2 is in minutes and B3/B4 should be
times not dates. I'm wasn't sure how you wanted the macro to start so I put
it in the workbook_open procedure. You could also put a call to it from a
Worksheet_Change event for cell B1.

If you've got any questions I'd be glad to assist.

Mike



"Matt" wrote:

I would like to run a macro, "Perform_Tasks", on a scheduled basis. I would
like users to be able to select certain criteria for this macro, as such:

A B C....
1 Enable/Disable: Enabled
2 Interval: 15
3 Begin Time: 07:00
4 End Time: 13:00

B1:B4 would be user changable values and would dictate how often the macro
kicks off automatically, if enabled. (B1 is a toggle switch between Enable or
Disabled).

Any suggestions?

Matt

Schedule a macro with user defined settings
 

Mike -

When I change from Enabled to Disabled, it runs the macro one more
time...I'd like it to kill the process as soon as the change is made. Also,
if I change from Disabled to Enabled, nothing happens. I think this may be a
workbook change feature, but am not sure how to implement it. Any help would
be greatly appreciated.

Thanks,

Matt


"Matt" wrote:

Thanks Mike..I'll let you know if I have any questions.

Matt


"crazybass2" wrote:

Matt,

You can remove the Sheet1.cells(2,4) = now statement toward the bottom.
That's what I was using to know the procedure was being called properly.

Just make sure that the other two lines in "Preform_Tasks" are the last two
lines in your "Perform_Tasks" macro.

Mike

"crazybass2" wrote:

Matt,

I've got a workable version here.

Place this in a module:

Public nexttime As Double
Public interval As Double
Public start As Date
Public endtm As Date
Public Const RunWhat = "Perform_Tasks"
Sub scheduler()
interval = TimeSerial(0, 0, Sheet1.Range("B2"))
start = Sheet1.Range("B3")
endtm = Sheet1.Range("B4")
nexttime = Now + interval
If Sheet1.Range("B1") = "Enabled" Then
If Time = start And Time + interval < endtm Then
Application.OnTime nexttime, RunWhat
Else: Application.OnTime start, RunWhat
End If
End If
End Sub
Sub endscheduler()
On Error Resume Next
Application.OnTime lasttime, RunWhat, , False
Application.OnTime nexttime, RunWhat, , False
End Sub
Sub Perform_Tasks()
lasttime = nexttime
scheduler
Sheet1.Cells(2, 4) = Now
End Sub


And put this is the ThisWorkbook module.

Private Sub Workbook_Open()
scheduler
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
endscheduler
End Sub


If you've already got a Workbook_Open or Workbook_BeforeClose event just add
the lines to those. The interval in B2 is in minutes and B3/B4 should be
times not dates. I'm wasn't sure how you wanted the macro to start so I put
it in the workbook_open procedure. You could also put a call to it from a
Worksheet_Change event for cell B1.

If you've got any questions I'd be glad to assist.

Mike



"Matt" wrote:

I would like to run a macro, "Perform_Tasks", on a scheduled basis. I would
like users to be able to select certain criteria for this macro, as such:

A B C....
1 Enable/Disable: Enabled
2 Interval: 15
3 Begin Time: 07:00
4 End Time: 13:00

B1:B4 would be user changable values and would dictate how often the macro
kicks off automatically, if enabled. (B1 is a toggle switch between Enable or
Disabled).

Any suggestions?

Matt

Schedule a macro with user defined settings
 

Matt,

Replace the code in your module with the following mod:

Public nexttime As Double
Public lasttime As Double
Public interval As Double
Public start As Date
Public endtm As Date
Public Const RunWhat = "Perform_Tasks"
Sub scheduler()
interval = TimeSerial(0, 0, Sheet1.Range("B2"))
start = Sheet1.Range("B3")
endtm = Sheet1.Range("B4")
If Sheet1.Range("B1") = "Enabled" Then
If Time = start And Time + interval < endtm Then
nexttime = Now + interval
Application.OnTime nexttime, RunWhat
Else: Application.OnTime start, RunWhat
End If
Else: endscheduler
End If
End Sub
Sub endscheduler()
On Error Resume Next
Application.OnTime nexttime, RunWhat, , False
Application.OnTime lasttime, RunWhat, , False
End Sub
Sub Perform_Tasks()
lasttime = nexttime
scheduler
End Sub

And add the following procedure to your ThisWorkbook module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Sheet1.Range("B1")) Is Nothing Then scheduler
End Sub


The last bit is the event you need to have the Enable/Disable trigger the
start of the macro.

Mike


"Matt" wrote:

Mike -

When I change from Enabled to Disabled, it runs the macro one more
time...I'd like it to kill the process as soon as the change is made. Also,
if I change from Disabled to Enabled, nothing happens. I think this may be a
workbook change feature, but am not sure how to implement it. Any help would
be greatly appreciated.

Thanks,

Matt


"Matt" wrote:

Thanks Mike..I'll let you know if I have any questions.

Matt


"crazybass2" wrote:

Matt,

You can remove the Sheet1.cells(2,4) = now statement toward the bottom.
That's what I was using to know the procedure was being called properly.

Just make sure that the other two lines in "Preform_Tasks" are the last two
lines in your "Perform_Tasks" macro.

Mike

"crazybass2" wrote:

Matt,

I've got a workable version here.

Place this in a module:

Public nexttime As Double
Public interval As Double
Public start As Date
Public endtm As Date
Public Const RunWhat = "Perform_Tasks"
Sub scheduler()
interval = TimeSerial(0, 0, Sheet1.Range("B2"))
start = Sheet1.Range("B3")
endtm = Sheet1.Range("B4")
nexttime = Now + interval
If Sheet1.Range("B1") = "Enabled" Then
If Time = start And Time + interval < endtm Then
Application.OnTime nexttime, RunWhat
Else: Application.OnTime start, RunWhat
End If
End If
End Sub
Sub endscheduler()
On Error Resume Next
Application.OnTime lasttime, RunWhat, , False
Application.OnTime nexttime, RunWhat, , False
End Sub
Sub Perform_Tasks()
lasttime = nexttime
scheduler
Sheet1.Cells(2, 4) = Now
End Sub


And put this is the ThisWorkbook module.

Private Sub Workbook_Open()
scheduler
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
endscheduler
End Sub


If you've already got a Workbook_Open or Workbook_BeforeClose event just add
the lines to those. The interval in B2 is in minutes and B3/B4 should be
times not dates. I'm wasn't sure how you wanted the macro to start so I put
it in the workbook_open procedure. You could also put a call to it from a
Worksheet_Change event for cell B1.

If you've got any questions I'd be glad to assist.

Mike



"Matt" wrote:

I would like to run a macro, "Perform_Tasks", on a scheduled basis. I would
like users to be able to select certain criteria for this macro, as such:

A B C....
1 Enable/Disable: Enabled
2 Interval: 15
3 Begin Time: 07:00
4 End Time: 13:00

B1:B4 would be user changable values and would dictate how often the macro
kicks off automatically, if enabled. (B1 is a toggle switch between Enable or
Disabled).

Any suggestions?

Matt

Schedule a macro with user defined settings
 

Mike -

I works great...thanks

Matt


"crazybass2" wrote:

Matt,

Replace the code in your module with the following mod:

Public nexttime As Double
Public lasttime As Double
Public interval As Double
Public start As Date
Public endtm As Date
Public Const RunWhat = "Perform_Tasks"
Sub scheduler()
interval = TimeSerial(0, 0, Sheet1.Range("B2"))
start = Sheet1.Range("B3")
endtm = Sheet1.Range("B4")
If Sheet1.Range("B1") = "Enabled" Then
If Time = start And Time + interval < endtm Then
nexttime = Now + interval
Application.OnTime nexttime, RunWhat
Else: Application.OnTime start, RunWhat
End If
Else: endscheduler
End If
End Sub
Sub endscheduler()
On Error Resume Next
Application.OnTime nexttime, RunWhat, , False
Application.OnTime lasttime, RunWhat, , False
End Sub
Sub Perform_Tasks()
lasttime = nexttime
scheduler
End Sub

And add the following procedure to your ThisWorkbook module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Not Intersect(Target, Sheet1.Range("B1")) Is Nothing Then scheduler
End Sub


The last bit is the event you need to have the Enable/Disable trigger the
start of the macro.

Mike


"Matt" wrote:

Mike -

When I change from Enabled to Disabled, it runs the macro one more
time...I'd like it to kill the process as soon as the change is made. Also,
if I change from Disabled to Enabled, nothing happens. I think this may be a
workbook change feature, but am not sure how to implement it. Any help would
be greatly appreciated.

Thanks,

Matt


"Matt" wrote:

Thanks Mike..I'll let you know if I have any questions.

Matt


"crazybass2" wrote:

Matt,

You can remove the Sheet1.cells(2,4) = now statement toward the bottom.
That's what I was using to know the procedure was being called properly.

Just make sure that the other two lines in "Preform_Tasks" are the last two
lines in your "Perform_Tasks" macro.

Mike

"crazybass2" wrote:

Matt,

I've got a workable version here.

Place this in a module:

Public nexttime As Double
Public interval As Double
Public start As Date
Public endtm As Date
Public Const RunWhat = "Perform_Tasks"
Sub scheduler()
interval = TimeSerial(0, 0, Sheet1.Range("B2"))
start = Sheet1.Range("B3")
endtm = Sheet1.Range("B4")
nexttime = Now + interval
If Sheet1.Range("B1") = "Enabled" Then
If Time = start And Time + interval < endtm Then
Application.OnTime nexttime, RunWhat
Else: Application.OnTime start, RunWhat
End If
End If
End Sub
Sub endscheduler()
On Error Resume Next
Application.OnTime lasttime, RunWhat, , False
Application.OnTime nexttime, RunWhat, , False
End Sub
Sub Perform_Tasks()
lasttime = nexttime
scheduler
Sheet1.Cells(2, 4) = Now
End Sub


And put this is the ThisWorkbook module.

Private Sub Workbook_Open()
scheduler
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
endscheduler
End Sub


If you've already got a Workbook_Open or Workbook_BeforeClose event just add
the lines to those. The interval in B2 is in minutes and B3/B4 should be
times not dates. I'm wasn't sure how you wanted the macro to start so I put
it in the workbook_open procedure. You could also put a call to it from a
Worksheet_Change event for cell B1.

If you've got any questions I'd be glad to assist.

Mike



"Matt" wrote:

I would like to run a macro, "Perform_Tasks", on a scheduled basis. I would
like users to be able to select certain criteria for this macro, as such:

A B C....
1 Enable/Disable: Enabled
2 Interval: 15
3 Begin Time: 07:00
4 End Time: 13:00

B1:B4 would be user changable values and would dictate how often the macro
kicks off automatically, if enabled. (B1 is a toggle switch between Enable or
Disabled).

Any suggestions?

Matt