Application On time Question

How do I adapt the VBA code for Application.OnTime so it only run on Monday to Friday?

Sub Workbook_Open()

'Application.OnTime TimeSerial(8, 0, 0), "Macro5"    'Run the Import macro at 8 AM
Application.OnTime TimeValue("8:0:00"), "Macro5"
End Sub
JustincutAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Rory ArchibaldConnect With a Mentor Commented:
Depending on when you open the workbook, and what's in macro5, it may be simpler to just check before you schedule it:
Sub Workbook_Open()
    If Weekday(Date, vbmonday) < 6 then Application.OnTime TimeValue("08:00:00"), "Macro5"
End Sub

Open in new window

0
 
MichaelBusiness AnalystCommented:
Hi Justin,

maybe you can do it like this:

Sub Workbook_Open()
    'Application.OnTime TimeSerial(8, 0, 0), "Macro5"    'Run the Import macro at 8 AM
    Application.OnTime TimeValue("08:00:00"), "checkDay"
End Sub

Sub checkDay()
    If Weekday(Now(), vbMonday) > 5 Then
        Exit Sub
    Else
        Call Macro5
    End if
End Sub

Open in new window

0
 
GrahamSkanRetiredCommented:
I suggest that you put a test at the start of Macro5

Sub Macro5()
Dim dow As Integer

dow = Format(Now, "w")
If dow = 1 Or dow = 7 Then
    Exit Sub
End If
'...

End Su

Open in new window

b
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.