Running Multiple On time Codes

[ajw5173]

Hi,

I have a workbook that I want to run 2 Ontime codes. I want the workbook to save every 5 minutes but I also want it to close at 10PM. These codes work independently but I can't figure out how to get them to work together in the ThisWorkbook tab. I would like to have them both be wookbook_open if possible

This is the code:

Private Sub Workbook_Open()
    On Error Resume Next
 
    Application.OnTime VBA.TimeValue("22:00:00"), "SaveAndCloseWorkBook", , True
End Sub


Private Sub Workbook_WindowActivate(ByVal Wn As Window)
           Application.OnTime Now + TimeValue("00:05:30"), "Save1"
End Sub

 

[ISY]

Hi

Public sMacro As String
Public fireTime As Date

Sub startTimer(ByVal idx As Long, ByVal counter As Long, ByVal tick As String, ByVal TimerActive As Boolean)
    fireTime = Now + TimeValue("00:00:05")
    sMacro = "  'pullData " & idx & " , " & counter & ", " & Chr(34) & tick & Chr(34) & ", " & TimerActive & "'"
    Application.OnTime EarliestTime:=fireTime, Procedure:=sMacro, Schedule:=True
End Sub

Sub StopTimer(ByVal idx As Long, ByVal counter As Long, ByVal tick As String, ByVal TimerActive As Boolean)
    Application.OnTime EarliestTime:=fireTime, Procedure:=sMacro, Schedule:=False
End Sub

 

[ajw5173]

Thanks! would I place this in the Thiswookbook tab instead of what I have above?

My first macro calls "SaveAndCloseWorkBook" while my second calls "Save1"

Would I still need to reference these codes?

 

[ajw5173]

I tried to put the code into module 1 but I don't think that was correct.

 

[John_w]

I have a workbook that I want to run 2 Ontime codes. I want the workbook to save every 5 minutes but I also want it to close at 10PM. These codes work independently but I can't figure out how to get them to work together in the ThisWorkbook tab. I would like to have them both be wookbook_open if possible

Try this code.

In the ThisWorkbook module:

Option Explicit

Private Sub Workbook_Open()
    StartTimer
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    StopTimer
End Sub

In a standard module (e.g. Module1):

Option Explicit

Public Const SaveInterval As String = "00:05:00"
Public Const CloseTime As String = "22:00:00"

Public NextSaveTime As Date
Public CloseWorkbookTime As Date


Public Sub StartTimer()
    NextSaveTime = Now + TimeValue(SaveInterval)
    Application.OnTime EarliestTime:=NextSaveTime, Procedure:="SaveWorkbook", Schedule:=True
    If CloseWorkbookTime = 0 Then
        If Time < CloseTime Then
            CloseWorkbookTime = TimeValue(CloseTime)
        Else
            CloseWorkbookTime = Date + 1 + TimeValue(CloseTime)
        End If
        Application.OnTime EarliestTime:=CloseWorkbookTime, Procedure:="CloseWorkbook", Schedule:=True
    End If
End Sub

Public Sub StopTimer()
    On Error Resume Next
    Application.OnTime EarliestTime:=NextSaveTime, Procedure:="SaveWorkbook", Schedule:=False
    Application.OnTime EarliestTime:=CloseWorkbookTime, Procedure:="CloseWorkbook", Schedule:=False
    On Error GoTo 0
End Sub

Public Sub SaveWorkbook()
    'Save this workbook and reschedule the timer
    ThisWorkbook.Save
    StartTimer
End Sub

Public Sub CloseWorkbook()
    If Workbooks.Count > 1 Then
        'If more than 1 workbook is open then save and close only this workbook
        ThisWorkbook.Close True
    Else
        'Otherwise, if only 1 workbook is open then close the Excel app, which also closes this workbook
        ThisWorkbook.Save
        Application.Quit
    End If
End Sub

Save, close and reopen the macro workbook to test it.