In one of the threads on this site regarding EnableEvents, someone said
that
EnableEvents = False applies only to the sub it is in.

That is completely false. EnableEvents is a global setting that remains in
effect even after the workbook that sets it is closed. The setting remains
in effect until it is reset with code or Excel itself closes. It is by no
means a "per procedure" setting.

--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

"DJ" wrote in message
...
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!