Tom - Help - Workbook keeps reopening

Hi Sam,

Thanks a lot for writing. You know what, I did not experience that problem with the mock workbook when I sent you the code originally, but after seeing your post here I tried it again and you are absolutely correct.

I tested this situation under numerous conditions but could not figure an elegant solution. The unelegant workaround, as you probably discovered yourself, is to close Excel and reopen it, which is inconvenient when other applications are open.

I just don't know the answer right now (probably something obvious we're missing), but if anyone reading this can shed light, please chime in.

Thanks again for the follow up.

Tom Urtis

Thanks Tom - really appreiate your help
BTW we had a severe storm the day after and the staff here really appreciated your procedure that I incorporated.

Glad I could help.

I keep thinking this solution might be found in the VBE somehow. I'll keep thinking about it, and there are some highly talented programmers who visit this site and may well know the answer.

T.U.

Tom Sam - Help - Workbook keeps reopening

Tom / Sam with application Ontime you must
schedule it to shut down via Schedule:=False
Also be careful how you call it eg

Public Sub Recalculate()
Calculate
Application.OnTime earliesttime:=Now + TimeValue("00:01:00"), _
procedure:="Recalculate"
End Sub

Public Sub Recalculate()
Application.OnTime earliesttime:=Now + TimeValue("00:01:00"), _
procedure:="Recalculate",Schedule:=False
End Sub

WILL NOT CANCEL THE JOB. This is because the procedure AND the Time variable are linked.
So you have to do something like this;

Public Thistime As Double

Public Sub Recalculate()
Calculate
Thistime = Now + TimeValue("00:01:00")
Application.OnTime Thistime, Procedure:="Recalculate"
End Sub

Public Sub stopCalc()
Application.OnTime Thistime, Procedure:="Recalculate", Schedule:=False
End Sub

Also call it @ the Workbook before close event
to STOP the calc routine from running. This is what
you were experiencing when you ran the routine.
Because it wasn't stoped properly it would load
up every minute.

HTH

Ivan

Thanks Ivan, that's it, plus theory ramblings.......

Thank you Ivan, that's it. I totally forgot about that fourth parameter (and its syntax) of setting it to False, for cancelling the next scheduled run stored in the module.

Your way is definitely the way to go, but thinking about this now, one other awkward way (not recommended, just theory ramblings here) is if the name of the procedure is changed, an error would take place at the next scheduled procedure call because the macro couldn't be found. Also, a comment might do it at the ThisTime instruction, which should end it at the next procedure call. Neither method is practical, I'm just testing my understanding and could be wrong.

Thanks again for your help.

Tom Urtis

: Glad I could help. : I keep thinking this solution might be found in the VBE somehow. I'll keep thinking about it, and there are some highly talented programmers who visit this site and may well know the answer. : T.U. :

Re: Thanks Ivan, that's it, plus theory ramblings.......

Thank you Ivan and Tom - my head is spinning, how do you fellows work out all this stuff. Thank you Ivan, that's it. I totally forgot about that fourth parameter (and its syntax) of setting it to False, for cancelling the next scheduled run stored in the module. Your way is definitely the way to go, but thinking about this now, one other awkward way (not recommended, just theory ramblings here) is if the name of the procedure is changed, an error would take place at the next scheduled procedure call because the macro couldn't be found. Also, a comment might do it at the ThisTime instruction, which should end it at the next procedure call. Neither method is practical, I'm just testing my understanding and could be wrong. Thanks again for your help.

: Tom / Sam with application Ontime you must : schedule it to shut down via Schedule:=False : Also be careful how you call it eg : : Public Sub Recalculate() : Public Sub Recalculate() ,Schedule:=False : WILL NOT CANCEL THE JOB. This is because the procedure AND the Time variable are linked. : So you have to do something like this; : Public Thistime As Double : Public Sub Recalculate() : Thistime = Now + TimeValue("00:01:00") : Application.OnTime Thistime, Procedure:="Recalculate" : Public Sub stopCalc() : Application.OnTime Thistime, Procedure:="Recalculate", Schedule:=False : : Also call it @ the Workbook before close event : to STOP the calc routine from running. This is what : you were experiencing when you ran the routine. : Because it wasn't stoped properly it would load : up every minute. : : HTH : : Ivan :

Sub stopCalc falls over

Ivan question to clear up something for a VBA novice(self taught and trying to get my mind around it).

Why does "Double" do for a variable?

Also Public Sub stopCalc()falls over with "Method 'OnTime' of Object'_Application' failed - it doesn't like Schedule:=False

Your last comment mentioned to call it @ the Workbook before close event, I'm not exactly sure what is required here - could you elucidate.

Sorry to keep bothering you with what are probably basics. Tom / Sam with application Ontime you must