Continuous Do Loop desired, almost

[mrdavie007]

I am writting several macros the goal of which is to update my worksheets based on new information from the web when it becomes available. I've written the entire update process but I would like this to happen at regular intervals for perhaps days or even up to a week at a time. The problem comes in when I want to stop the loop. I don't want to just stop the code excecution because it may be in the middle of performing an update and stopping in the middle of the process would leave undesired affects on the worksheet. Any ideas on how something like this could be done cleanly (i.e. no Ctrl + Break) would be appreciated. I'm trying to keep things looking proffesional, at least on the external even if the code isn't so pretty.

 

[Jaye7]

I am no expert, however, you could put the following script at the start of your macro.

Application.EnableCancelKey = xlDisabled

You must put the following at the end of your code though, otherwise you will have problems later if you do want to stop code running.

Application.EnableCancelKey = xlEnabled

Or you can put a value in a cell and if it equals that value then the script would either have the state of enabled or disabled.

If Range("A1").value = "0" then
Application.EnableCancelKey = xlDisabled
else
Application.EnableCancelKey = xlEnabled
end if