Excel VBA User form validation of date to exclude Sat, Sun and Holidays

[HEALTHSTAFF]

Hello,
When using a date picker or textbox in a user form can i use data validation to message users to select a new date if a Saturday, Sunday or a list of dates on the worksheet. I can do this on a cell using the following formula
data validation Formula

=(WEEKDAY(A2,2)<6)*ISNA(MATCH(A2,K1:K9,0))

Can this be done on a user form when OK button pressed and before any data pasted to worksheet. The OK button code is as follows

Private Sub OK_Click()Dim LastRow As Long, ws As Worksheet


    Set ws = Sheets("Sheet1")


    LastRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1 
    ws.Range("B" & LastRow).Value = TextBox1.Value 
    ws.Range("C" & LastRow).Value = TextBox3.Value 
    ws.Range("D" & LastRow).Value = TextBox2.Value
    ws.Range("E" & LastRow).Value = TextBox5.Value
    ws.Range("A" & LastRow).Value = Calendar1.Value
End Sub

Any Help Greatly appreciated

Craig

 

[dmt32]

Hi,
you can use both those functions in VBA to test your calendar control or textbox for entered value


untested but hopefully, gives you something to work with

Private Sub OK_Click()
    Dim HolidayDates As Variant, m As Variant
    Dim UserDate As Date
    Dim DateNotAllowed As Boolean
    Dim LastRow As Long
    Dim ws As Worksheet
    
'*************************************************************************************************************
'******************************************DATE ENTRY METHOD**************************************************


'If you are using a calendar control then enable this line
'UserDate = Me.Calendar1.Value


'if using a textbox then enable this line and change Textbox name as required
    UserDate = DateValue(Me.TextBox1.Value) 
    
'*************************************************************************************************************


    Set ws = ThisWorkbook.Worksheets("Sheet1")
    
'sheet with holiday dates
    HolidayDates = Worksheets("Holidays").Range("K1:K9").Value2
    
'check date is not weekend or on holiday list
    m = Application.Match(CLng(UserDate), HolidayDates, 0)
    DateNotAllowed = CBool(Not IsError(m) Or Weekday(UserDate, vbMonday) > 5)
    
    If DateNotAllowed Then
    
        MsgBox UserDate & Chr(10) & "You Have Selected A " & _
        IIf(Not IsError(m), "Holiday", "Weekend") & " Date", 48, "Date Entry Not Allowed"


    Else
        
        LastRow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row + 1
        ws.Range("A" & LastRow).Value = UserDate
        ws.Range("B" & LastRow).Value = TextBox1.Value
        ws.Range("C" & LastRow).Value = TextBox3.Value
        ws.Range("D" & LastRow).Value = TextBox2.Value
        ws.Range("E" & LastRow).Value = TextBox5.Value
        
        MsgBox "Record Added", 64, "Record Added"
        
    End If
End Sub

Change name of sheet shown in red where holiday dates are held as required.
Also, enable the line shown in Date Entry Section for the date entry control you are using.

If need to test other controls then suggest you make DateNotAllowed a Function to save repeating code.

Hope Helpful

Dave

 

[HEALTHSTAFF]

Thanks for your time Dave