Dont Allow Textbox Date to be a Holiday

[DrummerAndy]

Hello,

We use Excel to organize our testing enhancements. When we first open the template, a form displays to enter information regarding the enhancement including the Begin and End dates.

I would like to code the text boxes where the dates are entered to check whether or not each date is a state holiday and return an error message if it is.

I already have the following code to validate that the value entered is a date in the first place.

Private Sub txtSTB_Exit(ByVal Cancel As MSForms.ReturnBoolean)
txtSTB.BackColor = &HFFFFFF
 
With txtSTB
     If IsDate(.Value) Then
          .Value = Format$(.Value, "mm/dd/yyyy")
     Else
          txtSTB.BackColor = &HFF
          MsgBox "The System Test Begin Date is not valid."
          .SelStart = 0
          .SelLength = Len(.Text)
          Cancel = True
     End If
End With
 
End Sub

Finally, I have a sheet in the workbook called "State Holidays" where I have 28 holidays listed (14 for last year and 14 for this year) in chronological order using their numeric value (ie. 40910 for 1/2/2012) in Column C. I have also named those cells 'Holidays' in Excel.

I hope this is enough information for someone to help me.

Thanks in advance!

 

[AlphaFrog]

Private Sub txtSTB_Exit(ByVal Cancel As MSForms.ReturnBoolean)
txtSTB.BackColor = &HFFFFFF
 
With txtSTB
     If IsDate(.Value) Then
          .Value = Format$(.Value, "mm/dd/yyyy")
[COLOR="Red"]          If Not Range("Holidays").Find(Format(.Value, "0")) Is Nothing Then
                MsgBox "Date entered is a holiday. ", vbCritical, "Invalid Date"
          End If[/COLOR]
     Else
          txtSTB.BackColor = &HFF
          MsgBox "The System Test Begin Date is not valid."
          .SelStart = 0
          .SelLength = Len(.Text)
          Cancel = True
     End If
End With
 
End Sub

 

[DrummerAndy]

That didn't work. However I was able to make it work using this code:

Private Sub txtTPDB_Exit(ByVal Cancel As MSForms.ReturnBoolean)
txtSTB.BackColor = &HFFFFFF
 
Dim namedRange As Range
Dim ws As Worksheet
Set ws = Worksheets("State Holidays")
 
With txtSTB
    If IsDate(.Value) Then
        '/ format it
        .Value = Format$(.Value, "mm/dd/yyyy")
[COLOR=red]           For Each namedRange In ws.Range("Holidays")[/COLOR]
[COLOR=red]               If namedRange.Value = Format$(txtSTB.Value, "0") Then[/COLOR]
[COLOR=red]                   txtSTB.BackColor = &HFF[/COLOR]
[COLOR=red]                   MsgBox txtSTB.Value & " is a holiday. ", vbCritical, "Cannot Enter Holiday!"[/COLOR]
[COLOR=red]                   .SelStart = 0[/COLOR]
[COLOR=red]                   .SelLength = Len(.Text)[/COLOR]
[COLOR=red]                   Cancel = True[/COLOR]
[COLOR=red]               End If[/COLOR]
[COLOR=red]           Next[/COLOR]
     Else
        '/ error
        txtTPDB.BackColor = &HFF
        MsgBox "The System Test Begin Date is not valid."
        .SelStart = 0
        .SelLength = Len(.Text)
        Cancel = True
    End If
End With
End Sub

Thanks for your help anyway.