In one of the threads on this site regarding EnableEvents, someone said that
EnableEvents = False applies only to the sub it is in. When another sub is
called, it automatically begins with the default of EnableEvents = True.

In my series of subs, I haven't seen anything to suggest this understanding
is wrong.

"JLatham" wrote:

That is pretty much correct - the initial routine sets up the scheduled
events and that's actually the last time it runs. The other routines, such
as Update1, Update2, etc. are then called by the system at the scheduled
times.

By placing the test for the flag value at the start of the routines called
via scheduling, you're not terminating the scheduled events, you're just
keeping them from doing any work. Any event that is 'interrupted' because
the flag is set to STOP is not going to get performed at all while the flag
is set that way - the routine won't be called again until its next scheduled
time.

By the way, I just noticed in the Update1() code you provided, that you
never enable events or alerts again after turning them off - they're going to
stay off unless your code re-enables them again somewhere (after the call to
run Update2 ??).

"DJ" wrote:

My guess is that the schedule sub (seen in one of my resposes to JLatham) is
read/compiled once -- changes to the flag after the initial reading aren't
seen.

I'm reading up on "Change" events to see if putting the flag in a range
designated this way can make it so the macro can be stopped immediately,
rather than waiting to be stopped in the next scheduled event.

"Carlo" wrote:

That's strange, why shouldn't it work in the schedule sub?

But if it works now, we are happy :)

If you wanna show us the code for the schedule sub, we can
try to find out, why it doesn't work.

Cheers Carlo

"DJ" wrote:

Putting the "flag code" in the begining or in the schedule loop (allowing
Update1 to be called only if the flag is True/Go) does not work. But putting
it in Update1 does work.

Thanks Carlo and JLatham!

"JLatham" wrote:

See how Carlo's suggestion works, and if it solves the problem, then we don't
need to go any further. You could modify your Update1 process to 'abort'
with code something like this (assuming your flag cell is at G3 on the Data
sheet - change cell reference as needed. The 'flag' to stop would be the
word "STOP" (all caps) in that cell:

Sub Update1()
Windows("Tracker.xls").Activate
If ActiveWorkbook.Worksheets("Data").Range("G3")="STO P" Then
Exit Sub ' prevents data refresh
End If
€˜Needed to prevent Update2 from running before Refresh is complete
... and continue on with your existing code.

You could even set up Data Validation for your flag cell to allow choice of
2 options, RUN and STOP to prevent accidental misspelling by the user.

"Carlo" wrote:

Hey DJ

just a thought:
why don't you set a flag and let Macro1 run according to this flag.
Let's say your Flag is A1 in Sheet1 then you could do following:

sub macro1()
if sheets("sheet1").cells(1,1).value = "True" then
'do your schedule thing, or whatever
end if
end sub

that wouldn't prevent excel from running macro1 but it would stop
the scheduled events.

Oh and btw: try to rename your macros and modules for a better
overview.

Hope that helps

Carlo

"DJ" wrote:

There are quite a few threads on stopping macros, Ctrl Break, and
EnableCancelKey, but none have helped me. I am a new user of VBA and
programming in general, so there may be something very fundamental that Im
missing. Is there some underlying setting that can prevent Ctrl Break from
working (other than EnableCancelKey = False)? Does a macro have to be
executing a command for Ctrl Break to work, or can it be waiting to execute
the next command?

I have a series of subs, all within Module 1 (Excel 2003). Macro1 sets a
schedule for calling the other subs, most of the time this macro is just
waiting for the next event. The series of subs is looped through hourly for
several days. It does what I want it to do, but I want the user to be able
to stop Macro1 from calling further events before the schedule is finished.
So far, my only successful way of doing this is to close the file, and
re-open with macros disabled. Neither Ctrl Break nor €œEsc€� stops the
schedule initially read by Macro1. Ive tried the following:

Pressing Ctrl Break and Esc in both the VBE and in the worksheet.
Two different computers and keyboards.
Making the first line of the code read: Application.EnableCancelKey = True (just to make sure this is on, although based on what I have read, this should be the default at the start of a sub.)
Adding Application.EnableCancelKey = xlErrorHandler (with On Error GoTo, etc.)
Removing all lines of code using EnableEvents = False, DisplayAlerts = False, and ScreenUpdating = False (just in case these could somehow affect the processing of Ctrl Break, although Ive nothing suggesting this).

Ctrl Break pressed while a called sub is running (other than during a
RefreshAll) causes the Error 18 box to appear, and in VBE, the sub can be
reset, but this does not stop Macro1 for continuing the schedule.

Any ideas would be greatly appreciated!