OnTime Method error

I need a macro to run at a certain time on Wednesday's, and then a certain time on the other days.

I would like to use a cell to reference that time, but right now, I am just hard coding it, but I an not get it to run.

Ultimately what I am looking for is something like this:

Private Sub Workbook_Open()
     If (Weekday = 3) Then
     
     Application.OnTime TimeValue(Sheet2("E11"), "OpenMaster"

     Else
     Application.OnTime TimeValue(Sheet2("E12")), "OpenMaster"

End Sub

But I was not getting that to work, so then I just did a standard time code to test it, but even then it didn't work.

Standard code:

Private Sub Workbook_Open()
     Application.OnTime TimeValue("17:10:00"), "OpenMaster"

End Sub

With the module code:

Sub OpenMaster()

Application.OnTime

  TimeValue ("17:10:00"), "OpenMaster"

Workbooks.Open "G:\Make Your Day Program\8h Grade\Master 8.xls", UpdateLinks:=3

End Sub

But it didn't work....why? What did I do wrong?

Hello dsrt16,

The syntax shown below is correct, provided the cell contains a Time value. Have you tried calling the macro from another procedure other than the Workbook_Open event?

Application.OnTime TimeValue(Sheet2("E11"), "OpenMaster"

If the cell contains a time only, then the sub is scheduled to run today or tomorrow at the specified time, whichever has not yet occurred.

If you want to schedule it later, you need to include the date.

Try this as a confidence-building exercise, and then modify the code incrementally from there. Demo will run 10 seconds after you run Test.

Sub Test()
    Application.OnTime Now() + #12:00:10 AM#, "Demo"
End Sub

Sub Demo()
    MsgBox "The date and time are " & Now
End Sub