Simple on/off swith to turn macro on/off?
 

Hi Guys,

I am creating a macro that will web query every two minutes or so
throughout the business day. What's the simplest way to be able to 'turn
it off' without hitting Esc to break the code?

Any suggestions welcome.

Ron

Simple on/off swith to turn macro on/off?
 

You could use OntIme to schedule it to run at a given time, and then each
time fire it off 2 minutes later. Within that routine, you could test a
worksheet cell, and if a certain value, then just abort. You just set that
cell when you want to abort.

Chip has a good page about OnTime at
http://www.cpearson.com/excel/ontime.htm

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ron" wrote in message
...
Hi Guys,

I am creating a macro that will web query every two minutes or so
throughout the business day. What's the simplest way to be able to 'turn
it off' without hitting Esc to break the code?

Any suggestions welcome.

Ron

Simple on/off swith to turn macro on/off?
 

Bob,

Thanks for that, I've previously used (and am grateful for) Chips article
about OnTime. Here I want the sheet to start qurying at the hit of a
button, and when a situation is indicated I want to be able to 'turn off'
for a varying period of time which depends on how long it takes me to
complete the next task, which is a manual thing.

Ron


"Bob Phillips" wrote in
:

You could use OntIme to schedule it to run at a given time, and then
each time fire it off 2 minutes later. Within that routine, you could
test a worksheet cell, and if a certain value, then just abort. You
just set that cell when you want to abort.

Chip has a good page about OnTime at
http://www.cpearson.com/excel/ontime.htm

Simple on/off swith to turn macro on/off?
 

I think you could do all of that with the technique that I described Ron.
The button could issue the first OnTime macro, loading the cell could stop
it, and the button could restart (and clear the cell)

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ron" wrote in message
...
Bob,

Thanks for that, I've previously used (and am grateful for) Chips article
about OnTime. Here I want the sheet to start qurying at the hit of a
button, and when a situation is indicated I want to be able to 'turn off'
for a varying period of time which depends on how long it takes me to
complete the next task, which is a manual thing.

Ron


"Bob Phillips" wrote in
:

You could use OntIme to schedule it to run at a given time, and then
each time fire it off 2 minutes later. Within that routine, you could
test a worksheet cell, and if a certain value, then just abort. You
just set that cell when you want to abort.

Chip has a good page about OnTime at
http://www.cpearson.com/excel/ontime.htm

Simple on/off swith to turn macro on/off?
 

Hi Bob,

I agree, I could do that. I'm not overly sure of the syntax (if any) to
kill any remaining 'ontime' requests. I don't want the application
resurrecting the previous 'ontime' requests.

If I had OnTime requests set to (say) every 2 minutes through the day,
could I cancel the remaing requests with code if the scenario Im looking
for flagged up?

Thanks for your help anyway Bob.

Ron



"Bob Phillips" wrote in
:

I think you could do all of that with the technique that I described
Ron. The button could issue the first OnTime macro, loading the cell
could stop it, and the button could restart (and clear the cell)

Simple on/off swith to turn macro on/off?
 

This is the sort of thing that I mean

Dim nTime As Date

Sub StartSchedule()
nTime = Now + Time(2, 0, 0)
Application.OnTime "ScheduledJob", nTime
End Sub

Sub StopSchedule()
Application.OnTime "ScheduleJob", nTime, False
End Sub

Sub ScheduledJob()
If Range("A1").Value Then
Application.OnTime "ScheduleJob", nTime, False
Else
nTime = Now + Time(2, 0, 0)
Application.OnTime "ScheduledJob", nTime
End If
End Sub

The StarSchedule and StopSchedule macros would be tied to Forms buttons, and
the OnTime macro would check if cell A1 is TRUE or not, if TRUE exits the
timer. You can the re-start it at any time.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ron" wrote in message
...
Hi Bob,

I agree, I could do that. I'm not overly sure of the syntax (if any) to
kill any remaining 'ontime' requests. I don't want the application
resurrecting the previous 'ontime' requests.

If I had OnTime requests set to (say) every 2 minutes through the day,
could I cancel the remaing requests with code if the scenario Im looking
for flagged up?

Thanks for your help anyway Bob.

Ron



"Bob Phillips" wrote in
:

I think you could do all of that with the technique that I described
Ron. The button could issue the first OnTime macro, loading the cell
could stop it, and the button could restart (and clear the cell)

Simple on/off swith to turn macro on/off?
 

Hi Bob,

Thanks for that. It was the ability to stop the OnTime that I was
concerned about. I'm no expert at this vba lark but I'm willing to learn
and I've just learnt something new. I'll be giving it a whirl.

Thanks again.

Ron



"Bob Phillips" wrote in
:

This is the sort of thing that I mean

Dim nTime As Date

Sub StartSchedule()
nTime = Now + Time(2, 0, 0)

Simple on/off swith to turn macro on/off?
 

Hi Ron,

It is also detailed on Chip's page.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ron" wrote in message
...
Hi Bob,

Thanks for that. It was the ability to stop the OnTime that I was
concerned about. I'm no expert at this vba lark but I'm willing to learn
and I've just learnt something new. I'll be giving it a whirl.

Thanks again.

Ron



"Bob Phillips" wrote in
:

This is the sort of thing that I mean

Dim nTime As Date

Sub StartSchedule()
nTime = Now + Time(2, 0, 0)