VBA ontime cancels schedule

Question

I wrote a macro that starts at 3:30 p.m. every workday when the workbook opens. When the workbook is closed, it tries to open itself the next time the macro runs. I tried turning the scheduler to false and I get an error message. The code is below. Anyone have any ideas why this is not working?

thanks

Private Sub Workbook_Open()
    Application.OnTime TimeValue("15:30:00"), "MacroTimeTest"
End Sub

public dtime as date

Sub MacroTimeTest()

    dtime = (Format(Application.Evaluate("workday(today(), 1)"), "DD/MM/YY") & " " & TimeValue("15:30:00"))

    'other code has been deleted doesn't affect dtime variable 
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    'I have tried replacing false with 0 etc but it didn't make a difference
    Application.OnTime earliesttime:=dtime, procedure:="MacroTimeTest", schedule:=False

End Sub

Answer 1

I think you should keep the link for a while so you can undo the action. You can only undo an action if it has not already been completed.

In the ThisWorkbooktype the following command to run the macro at 15:59, while the sheet is closed

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)

    On Error GoTo CouldNotCancel

    Application.OnTime dTime, "MacroTimeTest", , False
    Debug.Print "Cancelled task to run at " & dTime

    Debug.Print "Workbook close"

    Exit Sub


CouldNotCancel:
    Debug.Print "No task to cancel"

End Sub 

Private Sub Workbook_Open()
    Debug.Print "Workbook open"

    dTime = TimeValue("15:59:00")

    Debug.Print "Next run time " & dTime
    Application.OnTime dTime, "MacroTimeTest"

End Sub

Then add your macro to the module

Option Explicit
Public dTime As Date
Public Sub MacroTimeTest()

    'schedule next run
    dTime = TimeValue("15:59:00")

    'schedule next run
    Debug.Print "Scheduling next run at " & dTime

    Application.OnTime dTime, "MacroTimeTest"

    Debug.Print "Running macro"

End Sub

, dTime , .

, ​​MacroTimeTest, .

, VBA (Ctrl + G)