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!

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!

I'll give this a try . . .

"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!

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!

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!

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!

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!

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" 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!

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

"JLatham" wrote:

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" 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!