OnTime method only runs 1 time

J

JP

Hi

I have a macro that runs a sub at a specific time. I have
set up a local macro that sets the timer when the workbook
is first opened to run a global macro at a specific time.
I often don't check the computer where this workbook is
for days at a time so I want to be able to just open the
workbook once and have the macro run every day at 3:00.
The local code is:

Private Sub Workbook_Open()

Dim Activate_Online_Historical As String

Application.OnTime _
earliestTime:=TimeValue("15:00:00"), _
Procedure:="Activate_Online_Historical"

End Sub

It works the first day but not the next unless I close and
reopen. Thanks in advance.
 
K

Keith Willshaw

JP said:
Hi

I have a macro that runs a sub at a specific time. I have
set up a local macro that sets the timer when the workbook
is first opened to run a global macro at a specific time.
I often don't check the computer where this workbook is
for days at a time so I want to be able to just open the
workbook once and have the macro run every day at 3:00.
The local code is:

Private Sub Workbook_Open()

Dim Activate_Online_Historical As String

Application.OnTime _
earliestTime:=TimeValue("15:00:00"), _
Procedure:="Activate_Online_Historical"

End Sub
Click to expand...

Id suggest using the CustomProperties collection of the workbook

example

Public Sub SaveHistoricalTime(OpenTime as String)
On Error Resume Next
ActiveWorkbook.CustomDocumentProperties("OnTime").Delete

ActiveWorkbook.CustomDocumentProperties.Add Name:="OnTime",
LinkToContent:=False, _
Type:=msoPropertyTypeString, value:=OpenTime

End Sub
This will be saved with the workbook

Keith
 
Top