Ask for VBA code for counting days between two dates and activate mandatory input if result as “less than 4”?

Question

I am a fresher in VBA and want to do a VBA as below conditions, could anyone help me? Please……

Cell J2 us set to be today()
Cell J4 can be set another date by user
Cell L4 set a formula =NETWORKDAY(J2-J4)-1, to calculate the exact days between 2 dates in J2 & J4
if the outcome in L4 is <4, user could continue to fill in the form;
Otherwise, 2 msg box pop up in sequence
- msgbox : 'Application takes at least 4-7 workings days';
- msgbox : 'Please pick another date'
return to Cell J4 which will be cleared as well
User should input the date in J4 again

here is my code, but it doesn't work:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Range("L4") >= 4 Then Exit Sub

Else

msg = MsgBox("Application takes at least 4-7 working days", vbOKOnly, "Reminder")

Range("J4").Select

Selection.ClearContents

msg = MsgBox("Please choose another date", vbOKOnly)

End If

End sub`

Black cat · Answer 1 · 2023-07-15T07:22:41.740

Before End Sub you have to re-enable events like this

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

If Range("L4") >= 4 Then
Application.EnableEvents = True
Exit Sub
Else
    msg = MsgBox("Application takes at least 4-7 working days", vbOKOnly, "Reminder")
    Range("J4").Select
    Selection.ClearContents
    msg = MsgBox("Please choose another date", vbOKOnly)
End If
Application.EnableEvents = True
End sub

or another implementation if only disable events if date is not good.

Private Sub Worksheet_Change(ByVal Target As Range)



If Range("L4") >= 4 Then
Exit Sub
Else
    Application.EnableEvents = False
    msg = MsgBox("Application takes at least 4-7 working days", vbOKOnly, "Reminder")
    Range("J4").Select
    Selection.ClearContents
    msg = MsgBox("Please choose another date", vbOKOnly)
End If
Application.EnableEvents = True
End sub

Sidenote: Gives more information about the error parameter (what is displayed, which line, relevant values etc.) this result more precise and faster answers mainly if more complex codes.

@joffee Edited. If you leave when >4 there also need enable events. — Black cat, Jul 15 '23 at 07:17

JNevill · Accepted Answer · 2023-07-17T13:04:05.207

0

First check to see if the Worksheet_Change Target is the date cell that the user is updating (J4). If it is, check L4 and pop your message box. Lastly turn events back on so it can trigger again next time there is a change.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Not Intersect(Range("J4"), Target) Is Nothing Then:
        If Range("L4").Value < 4 Then
             msg = MsgBox("Application takes at least 4-7 working days", vbOKOnly, "Reminder")
             Range("J4").ClearContents
             meg = MsgBox("Please choose another date", vbOKOnly)
        End If
    End If
    Application.EnableEvents = True
End Sub

edited Jul 17 '23 at 13:04

answered Jul 14 '23 at 15:45

JNevill

46,980
4
38
63

Thanks for your efforts. However, it doesn't work. No pop window at all. No event activated. Might the problem be that NETWORKDAYS formula set in L4? I dun know.....ToT – Joffee Jul 15 '23 at 05:13
Sorry for not responding sooner. I suspect that question was right-on. I've updated the logic to check the correct cell. – JNevill Jul 17 '23 at 13:04
Thousands thanks for your assistance. It works !! I'm much appreciated for your help!! U've solved a big big problem for me ! – Joffee Jul 21 '23 at 08:22

Ask for VBA code for counting days between two dates and activate mandatory input if result as “less than 4”?

2 Answers2