How to stop weekend date input

[Apple08]

Hi everyone

I want to avoid users to input a weekend or holiday date in the cell B10. I have a range of holidays and named as 'Holiday'. If user input a date such as 25/12/2018, then the date will automatically change to a day before the holiday or weekend e.g. 24/12/2018

If this is not possible to do it in the same field, I would consider to let users to input date in cell B11, then B10 will automatically work out the workday before the weekend or holiday input in B11.

Any help would be appreciated.

Regards
Elsa

 

[PCL]

Do you put the code in the sheet 's code
Select the sheet, then right clic, then View Code and paste the code

 

[Apple08]

I see, I have pasted them into Alt F11 module by mistakes. Mick's macro works well, but I don't know why PCL's code still doesn't work....

 

[MickG]

Try this Example file:-
NB:- There was a slight glitch but hopefully its now working Ok.
https://app.box.com/s/q3emrcsi5apfgrjs4j7b8nupc8b5dppj

NB:- If the previous code is working I should still replace it with this one.

 

[Apple08]

Hi PCL and Mick, they are both working now! Sorry for the confusion I thought it's my mistake!

Please can I ask if I want to add more fields other than only B10, do I have to repeat the code with different range or I can use multiple ranges in the same code please?

 

[PCL]

Is it a flexible range you need
Can you have a named range
Is the data in the same column ?
I made mistake in the IF selection

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Const WdAdd As String = "B10"
Const HRgName  As String = "Holidays"
Dim F
    If (Target.Address <> Range(WdAdd).Address) Then Exit Sub
    Application.EnableEvents = False
        Range(HRgName).Select
        Set F = Range(HRgName).Find(Target.Value, LookIn:=xlValues, Lookat:=xlWhole)
        If (Not F Is Nothing) Then
            MsgBox (" This is an holidays ")
            Target = ""
            Application.EnableEvents = True
            Exit Sub
        End If
        If ((Weekday(Target, 2) = 6) Or _
            (Weekday(Target, 2) = 7)) Then
            MsgBox (" This is a weekend ")
            Target = ""
            Application.EnableEvents = True
            Exit Sub
        End If
    
    Application.EnableEvents = True
End Sub

 

[Apple08]

I haven’t finished my spreadsheet yet, I am going to add more columns so assume they may be multiple fields such as B10, F12, H15 and M22.

 

[PCL]

Try next code
Adjust WkAdd to your needs

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
Const WkAdd As String = "$B$10,$F$12,$H$15,$M$22"
Const HRgName  As String = "Holidays"
Dim F
    If (Len(Replace(WkAdd, Target.Address, "")) = Len(WkAdd)) Then Exit Sub
    Application.EnableEvents = False
        Range(HRgName).Select
        Set F = Range(HRgName).Find(Target.Value, LookIn:=xlValues, Lookat:=xlWhole)
        If (Not F Is Nothing) Then
            MsgBox (" This is an holidays ")
            Target = ""
            Application.EnableEvents = True
            Exit Sub
        End If
        If ((Weekday(Target, 2) = 6) Or _
            (Weekday(Target, 2) = 7)) Then
            MsgBox (" This is a weekend ")
            Target = ""
            Application.EnableEvents = True
            Exit Sub
        End If
    
    Application.EnableEvents = True
End Sub

 

[Apple08]

Thank you PCL, this is very helpful, I appreciate it very much!

Hi Mick, is it possible to make your code in multi range too please?

 

[MickG]

Try this:-

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Dn [COLOR="Navy"]As[/COLOR] Range, Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Application.EnableEvents = False
Ray = ActiveWorkbook.Names("Holiday").RefersToRange
'[COLOR="Green"][B] Change range below to Suit.[/B][/COLOR]
[COLOR="Navy"]If[/COLOR] Not Intersect(Target, Range("B10, F12, H15, M22")) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]If[/COLOR] IsDate(Target) [COLOR="Navy"]Then[/COLOR]
       [COLOR="Navy"]Do[/COLOR] Until c >= UBound(Ray, 1) * 2
            c = c + 1
            [COLOR="Navy"]For[/COLOR] n = 1 To UBound(Ray)
               [COLOR="Navy"]If[/COLOR] Weekday(Target, 0) > 5 Or Target.Value = Ray(n, 1) [COLOR="Navy"]Then[/COLOR]
                 Target.Value = DateAdd("d", -1, Target.Value)
                 [COLOR="Navy"]Exit[/COLOR] For
               [COLOR="Navy"]End[/COLOR] If
           [COLOR="Navy"]Next[/COLOR] n
      [COLOR="Navy"]Loop[/COLOR]
   [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] If
Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Apple08]

Thank you Mick! You two are fabulous!