OnTime method only runs 1 time
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"), _ Procedu="Activate_Online_Historical" End Sub It works the first day but not the next unless I close and reopen. Thanks in advance. |
OnTime method only runs 1 time
Got it - Thanks Bob and Chip.
-----Original Message----- JP, The following code needs to be all on one single line Application.OnTime TimeValue("15:00:00"), "my_Procedure" -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "JP" wrote in message ... Hi Thanks for your reply Bob, but I can't figure out how to use your code. When replace Application.OnTime _ earliestTime:=TimeValue("15:00:00"), _ Procedu="Activate_Online_Historical" with Application.OnTime TimeValue ("15:00:00"), "), "my_Procedure" I get the error: Compile Error: Expected: End of Statement Do you know what I'm doing wrong? Thanks again. -----Original Message----- JP, In the macro that is run, or at the completion of that macro, you need to reset the OnTime method for 1 day hence Application.OnTime TimeValue ("15:00:00"), "), "my_Procedure" -- HTH ------- Bob Phillips ... looking out across Poole Harbour to the Purbecks "JP" wrote in message ... 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"), _ Procedu="Activate_Online_Historical" End Sub It works the first day but not the next unless I close and reopen. Thanks in advance. . . |
OnTime method only runs 1 time
"JP" wrote in message ... 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"), _ Procedu="Activate_Online_Historical" End Sub 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 |
All times are GMT +1. The time now is 03:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com