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
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
>
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
> >
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
> > >
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
> > > >
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
> > > > >
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
> > > > > >
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks