Date Validation

[Damo10]

Hi,

I have a userform to enter a start date for my sheet, what I would like to do is to have a check to see if the date that has been entered is a valid date. If the date is not valid then a messagebox to state that the date is not valid.

Here is the code for the userform.

Private Sub Confirm_Click()
On Error Resume Next
 
If Not (Me.DateSel.Text Like "##/##/##") Then
 Cancel = True
 MsgBox "Invalid date entered, start date has not been changed", vbOKOnly, "Invalid Date"
 
 Exit Sub
 End If
 
Run "Unprotect"
Sheets("Holidays").Range("D18") = CDate(Me.DateSel.Value)
Run "Month"
Run "Protect"
Unload Me
End Sub

Regards

 

[p45cal]

Assuming this is a true user form and not a bunch of cells on the sheet made to look like a form, then why not use a DateTime picker, that way, the date has to be a valid date, so you may only want to validate that it's in the correct range.

The picker can be found under Additional controls… Microsoft Date and Time Picker Control 6.0 (SP6) (or similar).

Then you could do something like:

Private Sub Confirm_Click()
If Year(DTPicker1.Value) < 2011 Or Year(DTPicker1.Value) > 2013 Then
  Cancel = True
  MsgBox "Invalid date entered, start date has not been changed", vbOKOnly, "Date out of range"
  Exit Sub
End If
Run "Unprotect"
Sheets("Holidays").Range("D18") = DTPicker1.Value
Run "Month"
Run "Protect"
Unload Me
End Sub

 

[Damo10]

Thanks that worked great