Compare two date fields in user form

[GMAN33]

Hi Gang

Using Excel 2000
I have a user form that has several fields on it. I have a text field that is Date 1 and a command button next to this that calls the Calendar Control 9 OCX and based on the user selection, this gets populated in the text field. I have two date fields that are like this. The purpose of this form is for a ticket submission to a programming group. So the first Date field represents the Request Date and the second Date field represents when the user needs their request to be completed.
I wrote some code to compare that the Due Date is not less the Request Date and that an error message pops up. The problem I am having is that it works on and off. It takes several times for me to select a lesser date for it to fire. The Due date does not reset itself and if I then go and select a date that is greater, after receiving the error, the error message still fires. Here is the code

Private Sub txtTime_Change()
If txtTime.Value < txtDate.Value Then
MsgBox ("The Due Date is less then the Request Date. Please enter another date")
Else
If txtTime.Value >= txtDate.Value Then
Exit Sub
End If
End If

End Sub

Sorry but I am very new to VBA and would appreciate and help

 

[Enkay62]

Hi.
As far as I can see in your code there could be a couple of causes.
An error of logic

You wrote:

....
Else
If....

Just remove 2d if...

so i'd write your code like this:


Private Sub txtTime_Change()
If txtTime.Value < txtDate.Value Then
MsgBox ("The Due Date is less then the Request Date. Please enter another date")
End If
End Sub

shouldn't it work yet, there could be a problem with type of data or formatting.

So try this:

If int(txtTime.Value) < int(txtDate.Value) Then
MsgBox ("The Due Date is less then the Request Date. Please enter another date")
End If
End Sub

Hope this helps.
Regards
Nick

 

[GMAN33]

Thank you Nick for the response. I did have your first suggestion and it seemed to work the first time around, but while I still had the form open and going back and forth trying to make it fire, it wouldn't clear the text box of txtTime when I received the error.

I tried your second suggestion but I was receiving Type Mismatch errors

Thanks again