Stopping a macro

[DJ]

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 I’m
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. I’ve 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 I’ve 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!

 

[Carlo]

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

 

[JLatham]

Hard to say for sure without seeing the main calling routine, RefreshAll?

The problem is in having set up a schedule. If you rewrite it to use a
'perpetual' loop and comparing time passed within the loop to determine when
an hour has passed, you'd be able to actually stop the processing with
[Ctrl]+[Break].

The first step is to see the code for the main controlling routine.

 

[DJ]

I am using a loop, but it ends with the last scheduled event. The user
enters the start time in G4, and the cells to the right each add an hour to
the time in the previous cell, creating the schedule. The main calling
routine, and the subsequent routine (with RefreshAll) follow:

Sub Schedule()
' Sets schedule for Refreshing and copying data.
Application.EnableCancelKey = True
Sheets("Data").Select
Range("g4").Select
Range(Selection, Selection.End(xlToRight)).Select
For Each Item In Selection
Application.OnTime Item.Value, "Update1"
Next Item
End Sub

Sub Update1()
Windows("Tracker.xls").Activate
‘Needed to prevent Update2 from running before Refresh is complete
Application.EnableEvents = False
‘Refresh Data
Application.DisplayAlerts = False
Sheets("Data").Select
ActiveWorkbook.RefreshAll
Application.Run "Update2"
End Sub

Hard to say for sure without seeing the main calling routine, RefreshAll?

The problem is in having set up a schedule. If you rewrite it to use a
'perpetual' loop and comparing time passed within the loop to determine when
an hour has passed, you'd be able to actually stop the processing with
[Ctrl]+[Break].

The first step is to see the code for the main controlling routine.

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 I’m
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. I’ve tried the following:


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!

 

[DJ]

I'll give this a try . . .

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

 

[JLatham]

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")="STOP" 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.

 

[DJ]

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!

 

[Carlo]

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]

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]

Yeah, you're right, didn't see that!

Carlo

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.

 

[JLatham]

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]

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]

That statement/presumption is true. I just didn't know the total flow of
code and what's going on in those Update# routines.

I'm kind of a belt-and-suspenders coder: if I alter a system condition, I do
my best to un-alter it at the end of my processing even if (in theory) things
are supposed to go back to the way they were. Overkill probably 99% of the
time, but there have also been cases of application updates interfering with
normal operations.

 

[Chip Pearson]

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)