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

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Hi

Try

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

Sub DoIt()
Run "StartOff"
'Your Code
End Sub
 
Upvote 0
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
 
Upvote 0
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,
 
Upvote 0
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())
 
Upvote 0
:confused: 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
:confused:
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,186
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top