How do I Run a Workbook_Open code only during the weekdays? (M-F)

Question

I have a series of Application.OnTime codes that need ran on specific days throughout the week (M-F). I don't need these codes to run on Saturday/Sunday. How do I code in to only run M-F? The Application.OnKey codes need to run every day. Thank you


    'Set keyboard shortcuts
    Application.OnKey "^+{UP}", "Sheet4.SpinButton1_SpinDown"
    Application.OnKey "^+{DOWN}", "Sheet4.SpinButton1_SpinUp"
    
    If Weekday(Now()) > 2 And Weekday(Now()) < 7 Then Call Workbook_Open
    
    Application.OnTime TimeValue("17:15:00"), "Saveit"
    Application.OnTime TimeValue("17:17:00"), "MASTER"
    Application.OnTime TimeValue("17:33:00"), "MASTER"
    Application.OnTime TimeValue("17:59:00"), "MASTER"
    Application.OnTime TimeValue("18:21:00"), "MASTER"
    Application.OnTime TimeValue("18:45:00"), "MASTER"
    Application.OnTime TimeValue("19:03:00"), "MASTER"
    Application.OnTime TimeValue("19:23:00"), "MASTER"
    Application.OnTime TimeValue("19:41:00"), "MASTER"

End Sub```

Uh, what did you try? I assumed you tried to check the day of the week and had a problem. What code did you use to check that, and what problem did you run into? — Ron Rosenfeld, Oct 17 '20 at 19:34
```Sub SecondSub() If Weekday(Now()) > 2 And Weekday(Now()) < 7 Then Call Workbook_Open End Sub``` — AND AND AND, Oct 17 '20 at 19:45
I'd put it within the `Workbook_Open` sub, bracketing the `.OnTime` functions. — Ron Rosenfeld, Oct 17 '20 at 19:55
I am confused as to what you mean for bracketing the .OnTime Function. I updated my code in the question — AND AND AND, Oct 17 '20 at 20:00
Remove `call workbook_open` and put `end if` after those lines you want to run on weekdays only. — Ron Rosenfeld, Oct 18 '20 at 00:34
I have assumed your code above is in the `workbook_open` routine. If not, it probably should be. `call workbook_open` doesn't make much sense to me as that routine will have run already when the workbook opened. What were you trying to accomplish with that call? — Ron Rosenfeld, Oct 18 '20 at 10:55
I have been trying about everything, I just tried to have the macro call itself to see if it would work. I have no idea how to make this work and anything to help make it work would be great help — AND AND AND, Oct 19 '20 at 03:11
Does your code run OK, upon workbook open, without the check for day of week? — Ron Rosenfeld, Oct 19 '20 at 10:30
Yes the code works perfectly whenever I don't have the "If" Statement. — AND AND AND, Oct 19 '20 at 12:13

score 0 · Accepted Answer · answered Oct 19 '20 at 12:25

If your code is working OK without the IF, then what I suggested in the comments should work.

Is this how you modified your code in response to my comments?

I used Date instead of Now but they should give the same results. Also the () after the Now are not needed in VBA

I also used >=2 since you indicated M-F, i.e: you wanted to include Monday

...
'Set keyboard shortcuts
    Application.OnKey "^+{UP}", "Sheet4.SpinButton1_SpinDown"
    Application.OnKey "^+{DOWN}", "Sheet4.SpinButton1_SpinUp"
    
If Weekday(Date) >= 2 And Weekday(Date) < 7 Then 
    
    Application.OnTime TimeValue("17:15:00"), "Saveit"
    Application.OnTime TimeValue("17:17:00"), "MASTER"
    Application.OnTime TimeValue("17:33:00"), "MASTER"
    Application.OnTime TimeValue("17:59:00"), "MASTER"
    Application.OnTime TimeValue("18:21:00"), "MASTER"
    Application.OnTime TimeValue("18:45:00"), "MASTER"
    Application.OnTime TimeValue("19:03:00"), "MASTER"
    Application.OnTime TimeValue("19:23:00"), "MASTER"
    Application.OnTime TimeValue("19:41:00"), "MASTER"

End If

End Sub

Since this is on Workbook_Open will I have to open this file every day for the code to reset itself? Thank you — AND AND AND, Oct 19 '20 at 12:46
With the testing that I have done so far with this code, it should be good to go. Thank you again @Ron — AND AND AND, Oct 19 '20 at 13:43
@ANDANDAND Workbook_Open will only fire when the workbook opens. For various methods of running this on a schedule, take a look at [VBA for smarties: Application.OnTime](https://www.snb-vba.eu/VBA_Application.OnTime_en.html) — Ron Rosenfeld, Oct 19 '20 at 14:40

How do I Run a Workbook_Open code only during the weekdays? (M-F)

1 Answers1