How can I made a marco run every hour and a half?

I need to run it every 90 mins. Thanks

[Dave Hawley]

Hi

Try

Sub StartOff()
Application.OnTime Now + TimeValue("00:90:00"), "DoIt"
End Sub

Sub DoIt()
Run "StartOff"
'Your Code
End Sub

I get an error on sub startoff

[SamS]

Make a small change as follows:

Public ThisTime As Double

Sub StartOff()
ThisTime = Now + TimeValue("00:90:00")
Application.OnTime earliesttime:=ThisTime, procedure:="DoIt"

Sub DoIt()
Run "StartOff"
'Your Code
End Sub

I am still getting an error BTW I am using Excel 97 if that matters

[Barrie Davidson]

On 2002-03-07 16:07, Anonymous wrote:
I am still getting an error BTW I am using Excel 97 if that matters

I think you just need to change the order of the sub named DoIt (see below).

Sub DoIt()
'Your Code
Run "StartOff"
End Sub

Regards,

[SamS]

silly me - you need to change
ThisTime = Now + TimeValue("00:90:00") to
ThisTime = Now + TimeValue("01:30:00") as you as time is in hh:mm:ss or you will get adata type mismatch (also forgot the end sub before sub DoIt())

Sam S that works! thanks

OK guys the file runs the Marco at the set interval, I changed it to 1 minute to check it, now how do I cut it OFF, when I close the file it will OPEN BACK UP BY ITS SELF! And start running the Marco. thanks

[robfo0]

I had this similar problem. I have some code that someone on this board helped me with (I'm sorry, i dont remember who ) shuts the worksheet down after it hasnt been used for a certain time, and eventhough the sheets were being closed, after the set time, they opened and closed again.

The problem youre having is that the code is still being run eventhough the file is closed. To stop this, you need to disable the counter. Im not the best person to tell you exactly how to do that but, heres the code I use:

In "ThisWorkbook" I have this code:

'This disables the time coutner and resets it when a caculation is made
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Call Disable
Call SetTime
End Sub

'This disables the time counter and resets it when a sheet is changed
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Excel.Range)
Call Disable
Call SetTime
End Sub

and in a module, i have this code:


Dim DownTime As Date

'This is the code that the macros in ThisWorkbook calls to set the timer
Sub SetTime()
DownTime = Now + TimeValue("00:15:00")
Application.OnTime DownTime, "ShutDown"
End Sub

'This is the code that the macros in ThisWorkbook calls to close the file
Sub ShutDown()
ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub

'This is the code that the macros in ThisWorkbook calls to disable the timer
Sub Disable()
On Error Resume Next
Application.OnTime EarliestTime:=DownTime, Procedure:="ShutDown", Schedule:=False
End Sub



Im assuming youll need something similar to the Disable code.

Hope this helps some