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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
Rory ArchibaldCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.