Restrict User To Enter Permissible Date Range Values Only In TextBox

[MrBlackd]

Hi, I have a form that has a specific textbox named DateBox and I want to restrict the user from entering any date later than 25 November 2013.
Any quick ideas on how to do this?

I have searched quickly but did not manage to find something suitable.
Thanks in advance for any reply.

I have tried something like this but it is not working...


Private Sub Datebox_Change()
Dim maxdate As String
maxdate = "25/11/2013"
If Datebox.Value > maxdate Then
MsgBox "You have entered an invalid date"
Else
Exit Sub
End If
End Sub

[Rick Rothstein]

Hi, I have a form that has a specific textbox named DateBox and I want to restrict the user from entering any date later than 25 November 2013.
Any quick ideas on how to do this?

I have searched quickly but did not manage to find something suitable.
Thanks in advance for any reply.

I have tried something like this but it is not working...


Private Sub Datebox_Change()
Dim maxdate As String
maxdate = "25/11/2013"
If Datebox.Value > maxdate Then
MsgBox "You have entered an invalid date"
Else
Exit Sub
End If
End Sub

I don't think you will want to use the Change event as it is fired for every character you type or delete... you won't have time to form your dates before you test the entry for being a date. I think you will need to use the Exit event... if the date is not proper, you can cancel the exit. Code like this should work for you...


Private Sub DateBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Len(DateBox.Value) Then
If IsDate(DateBox.Value) Then
If CDate(DateBox.Value) > DateSerial(2013, 11, 25) Then
MsgBox "You have entered an invalid date"
Cancel = True
End If
Else
MsgBox "You have entered an invalid date"
Cancel = True
End If
End If
End Sub

[MrBlackd]

Thank you for your reply....

I have actually decided to include the restriction on the save button of the form and not as a private sub.

[Rick Rothstein]

Thank you for your reply.... one more question...

what exactly does the line
Cancel=True

The Exit event fires when the focus leaves the TextBox... setting Cancel=True literally cancels the exiting of the TextBox and the cursor remains in the TextBox until the user enters a proper date. Since I did not want to trap the user in there forever (assuming they did not know what a proper date was), I allow the TextBox to be exited if it is empty. If you did not want to do that (in other words, allow the program to go on with bad data assuming the user does not heed the MessageBox's advice by correcting the data), then remove the Cance=True statements.

[MrBlackd]

I understand, no need though to remove the statements since as mentioned in my previous (edited) post I have decided to include the restrictions in the save button of the form.

But thanks for letting me know for future use, since I would not want the program to go on with bad data either.

Your help was valuable!