Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stopping a macro
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stopping a macro
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stopping a macro
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stopping a macro
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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stopping a macro
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stopping a macro
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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stopping a macro
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! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stopping a macro
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! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stopping a macro
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! |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stopping a macro
Yeah, you're right, didn't see that!
Carlo "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! |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stopping a macro
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! |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stopping a macro
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! |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stopping a macro
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. "DJ" wrote: 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! |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stopping a macro
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Stopping a Macro | Excel Programming | |||
Stopping a Macro | Excel Programming | |||
Stopping a Macro | Excel Programming | |||
stopping macro | Excel Programming | |||
My Macro keeps stopping?? | Excel Programming |