Running macro on computer time


Posted by Duncan on February 28, 2001 4:33 AM

A while ago I asked about running a macro on computer time and received an excellent reply from Dave (I think) using the application.ontime command
I am using it to run a web query - and each time it runs it tells the computer when to next run the query.
Only when the file is shut down it still knws the macro should be run and so opens up at the correct time - I wish to stop it doing this and so wrote a macro in Workbook_beforeclose
with the schedule:=false command added to applicatio.ontime.
However when the user clicks close and then cancel (instead of save cahnges or not save changes) I have a problem.
What happens is that the auto-run macro is disabled (which is one problem) but also when the user next closes the file I have an error as the computer no longer recognises that the macro is due to run and so doesn't accept the .ontime schedule := false!!

Any ideas or help greatly appreciated.
Sorry for the waffle
D.



Posted by David Hawley on February 28, 2001 4:58 AM

Hi Duncan

Yes I think it was me!

You should be able to overcome this with Boolean variable used like below:

Dim ToRun As Boolean

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Cancel = True Then
ToRun = True
Else
ToRun = False
End If
End Sub

Private Sub Workbook_Open()
ToRun = True
End Sub


Then use this variable in an If statement to control whether your on time macro runs or not.


Dave


OzGrid Business Applications