Help required developing a Date error message

I am developing a VB Userform which includes a start date (Date referral received) and end date (Case resolved date). I am wanting to develop a message box that illustrates a message when the user has put in an end date which is earlier than the start date e.g. "The Case Resolved Date cannot be before the Date Referral Received Date - please review the Case Resolved Date.

Can someone please assist me with some suggested code.

thanks

Try this

Hi Roy,

Thank you for your assistance.

Hopefully you might be able to address a further issue. I inserted the code you provided then I got an error on another piece of code 'Compile error: Ambiguous name detected:tbx8_Exit. As you can see in the code below there are three messages associated with date fields, two are regarding the date format and the other is the piece of code you greatfully sent me regarding end date before start date. I have highlighted the bit of code that gets highlighted with the error.

Also, when I entered your code and ran the userform it doesn't allow me the opportunity to change the date, instead it just closes the userform.

I would be in your debt in you can assist.



'Checks that the user has entered the correct date format for text box 1
Private Sub tbx1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim strDate As String
strDate = tbx1.Text
If Not IsDate(strDate) Then
MsgBox "Your date format is incorrect" & vbCrLf & "Enter a date as dd/mm/yyyy", _
vbOKOnly + vbExclamation
Cancel = True
End If
End Sub

'Checks that the user hasn't entered a Case Resolved Date with an earlier vale than Date Referral received
Private Sub tbx8_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Me
If Not IsDate(.tbx8.Value) Then
MsgBox "Please enter a valid date"
.tbx8.Value = Empty
ElseIf CDate(.tbx8.Value) < .tbx1.Value Then
MsgBox "Case Resolved Date cannot be earlier than Referral Received Date"
.tbx8.Value = Empty
End If
End With
End Sub

'Checks that the user has entered the correct date format for text box 8
Private Sub tbx8_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim strDate As String
strDate = tbx8.Text
If Not IsDate(strDate) Then
MsgBox "Your date format is incorrect" & vbCrLf & "Enter a date as dd/mm/yyyy", _
vbOKOnly + vbExclamation
Cancel = True
End If

End Sub

You cannot have two identical events for the same control, in this case you have two of these

Private Sub tbx8_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Roy,

So what your saying is that I can either have a date format error message OR an end date before start date error message BUT not both?

No, I'm saying that you cannot have two of the same events for one control. You just need to combine the two as mine actually does.

Hi Roy

Thanks for all your help. Please excuse my lack of VBA knowledge - I'm in the beginners league!

Can I ask you one more question without pushing the friendship. I entered the code you provided me that works well, however, the userform unloaded before I had the opportunity to enter a valid date. How do I prevent this from occuring?

regards
Graeme

Your code must unload the form. Have you added Unload Me to the code?

Hi Roy,


This is part of the code that I have:

'Checks that the Case resolve date is not before the Date referral received
Private Sub tbx8_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Me
If Not IsDate(.tbx8.Value) Then
MsgBox "Please enter a valid date"
.tbx8.Value = Empty
ElseIf CDate(.tbx8.Value) < .tbx1.Value Then
MsgBox "Case resolve date cannot be earlier than Date referral received"
.tbx8.Value = Empty
End If
End With

'Closes form
Unload Me
End Sub

'Checks that the user has entered the correct date format
Private Sub tbx1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim strDate As String
strDate = tbx1.Text
If Not IsDate(strDate) Then
MsgBox "Your date format is incorrect" & vbCrLf & "Enter a date as dd/mm/yyyy", _
vbOKOnly + vbExclamation
Cancel = True
End If

End Sub

thanks for your help
regards
Graeme

As I said, your code closes the UserForm

[/B]