Getting a Macro to run at a certain teme (Ontime)

denismccarthy

Board Regular
Joined
Dec 30, 2006
Messages
108
Hi,

I want to run a macro at a certain time, which I have the code. My question is how do I get the macro to run 15mins later if a certain criteria isn't met and to check ever 15 mins until criteria is met. Basically I want to check a date on a database to see if the day as moved on, which I'll be doing with a SQL line. Below is just a sample code I'm testing at home. I don't have SQL line as I'm not in work. Thanks for any help.

Private Sub Workbook_Open()

Application.OnTime TimeValue("13:21:00"), "MyMacro"
End Sub

Sub MyMacro()
'
' Macro1 Macro
' Macro recorded 15/06/2008 by McCarthy Family
'

'On Error Resume Next
Sheets("sheet1").Select
Range("a1").Select
Selection = "It works"
Dim rspCreate
If Dir("c:/test/", vbDirectory) = "" Then

MkDir "c:/test/"
End If

'
End Sub
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
Thanks John.

I'm getting an error. It's Highlighting the "Public Const..." in red and error message of

"Compile error:

Constants, fixed-lenght strings, arrays, user-defined types and Declare statements not allowed as Public members of object modules"

I'm running Excel 2002 SP3

Do I need a reference ticked under tools?

Thanks again

Option Explicit

Public RunWhen As Double
Public Const cStartTime = "09:30:00"
Public Const cEndTime = "16:00:00"
Public Const cRunInterval = "00:15:00"
Public Const cRunWhat = "Index_Analysis" 'name of procedure to run
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,329
Members
448,564
Latest member
ED38

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