Check if date is in future and empty textbox

[Bandito1]

Hey all,

I use this small code to check if the date entered is in the future or not.
If it is the user gets a messagebox and the cell should empty.

Private Sub txtStDate_Change()If CDate(txtStDate.Value) > Date Then
    MsgBox "The date of observation is in the future." & vbCrLf & "This is not possible, please correct!"
    txtStDate.Value = ""
    txtStDate.SetFocus
End If
End Sub

Without txtStDate.Value = "" it works. When i added i get error: Type mismatch.
I think because the code runs again when the cells gets emptied.

Someone knows how i can fix this?

 

[Yongle]

Try this (untested !)

Private Sub TxtStDate_Change()
    On Error Resume Next
    If CDate(TxtStDate.Value) > Date Then
        On Error GoTo 0
        MsgBox "The date of observation is in the future." & vbCrLf & "This is not possible, please correct!"
        Application.EnableEvents = False
        TxtStDate.Value = ""
        Application.EnableEvents = True
        TxtStDate.SetFocus
    End If
End Sub

Without txtStDate.Value = "" it works

Does your code work consistently correctly?

- looking at what is being evaluated in your test I would expect some valid dates to be rejected
- how are the dates being entered ?

 

[dmt32]

Hi,
try using the TextBox_Exit event which has cancel parameter

Private Sub txtStDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    With Me.txtStDate
    If IsDate(.Text) Then Cancel = CBool(CDate(.Value) > Date)
     If Cancel Then
            MsgBox "The date of observation is in the future." & vbCrLf & "This is not possible, please correct!"
            .Value = ""
        End If
    End With
End Sub

Dave

 

[Bandito1]

Hello Yongle and Dave,

Thanks for your replies. I appreciate that you take the time to help me.
@Yongle
Your code nearly works. The only problem i face is that i get the msgbox twice. I press "Ok" and it reappears. When i press "Ok" again the msgbox dissapears and the textbox is empty.

The code works consistently correct when i don't want to add that the textbox is being emptied (txtStDate.Value = "") when a date in the future is added.
The date is entered through the control "month view".

@dmt32
When i try your code i select a date in the future and i get no msgbox. When i click in the textbox and i click somewhere else your code fires and it works then. But when i dont click in the txtStDate the code doesn't fire and i can enter an date in the future.

 

[Yongle]

The date is entered through the control "month view".

- so the date is entered as a single string including the year
- hence why it works consistently correct

Private Sub TxtStDate_Change()
    Const Msg = "The date of observation is in the future." & vbCrLf & "This is not possible, please correct!"
    Dim T As Date, Message  As Boolean
    On Error Resume Next
    
    T = CDate(TxtStDate.Value)
    On Error GoTo Handling
    If T <= Date Then Exit Sub Else Message = True
        
Handling:
    TxtStDate.Value = ""
    If Message Then
        MsgBox Msg
        TxtStDate.SetFocus
    End If
End Sub

 

[dmt32]

,

when a date in the future is added. The date is entered through the control "month view".

It would have been helpful if mentioned this

try this update to your Change Event code & see if does what you want

Private Sub txtStDate_Change()
 With Me.txtStDate
    If Len(.Text) = 0 Then Exit Sub
     If IsDate(.Text) Then
        If CDate(.Value) > Date Then
            MsgBox "The date of observation is in the future." & vbCrLf & "This is not possible, please correct!"
            .Value = ""
        End If
     End If
    End With
End Sub

Dave

 

[Bandito1]

Hello both,

Sorry i've been out for a bit, but im back now.
Thanks again for your replies and thoughts.

@Yongle
Today it's 12 oktober 2019.
When i press 9-10 it enters the date correctly.
When i press 10-10 it enters the date correctly.
When i press 11-10 (yesterday) i get the msgbox that the date is in the future. This is wrong.
When i press 12-10 (today) i also get the msgbox.

When i press 13-10 and further it works correctly (those dates are indeed in the future).

So it seems like it sees today and days in the past as future days.

Thought it had to do with

[COLOR=#333333]If T <= Date Then Exit Sub Else Message = True[/COLOR]

but changing it didn't change the result output.

@dmt32
Your code has exact the same problem..
Can't be coincidence that both code have the same mistake?

The date that i get with =NOW() is the correct date, 12-10-2019

 

[Yongle]

May be due to the old chestnut AMERICAN dates vs UK dates

Test by inserting one line of code and inputting values where day and month are different to each other and both below 12

11 October will display as 10 November if my hunch is correct

below this line

On Error GoTo Handling

enter this line

MsgBox T

and check the results

 

[dmt32]

Hi,
same thinking as Yongle about USA dates

try changing this line

If CDate(.Value) > Date Then

to this

If DateValue(.Text) > Date Then

and see if resolves

Dave

 

[Bandito1]

Hey Guys,
@Yongle
Today it's 13-10-2019. When i press today 13-10-2019 i get the msgbox with 13-10-2019 and everything is fine. No msgbox that the date is in the future.
When i press 12-10-2019 i get the msgbox with 10-12-2019. So the day and month are other way around. I get the msgbox that this is date is in the future.

The date that is showed in the textbox is mm/dd/yyyy. While we use dd/mm/yyyy. Guess here is the problem right?
@dmt32
That doesn't resolve the problem. Guess the problem is what i typed above.