Check if date is in future and empty textbox

Bandito1

Board Regular
Joined
Oct 18, 2018
Messages
233
Office Version
  1. 2016
Platform
  1. Windows
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.

Code:
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?
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Try this (untested !)

Code:
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

:confused: 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 ?
 
Upvote 0
Hi,
try using the TextBox_Exit event which has cancel parameter



Code:
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
 
Upvote 0
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.
 
Last edited:
Upvote 0
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

Code:
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
 
Upvote 0
,

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

Code:
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
 
Upvote 0
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
Code:
[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
 
Last edited:
Upvote 0
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
Code:
On Error GoTo Handling
enter this line
Code:
MsgBox T

and check the results
 
Last edited:
Upvote 0
Hi,
same thinking as Yongle about USA dates

try changing this line

Code:
If CDate(.Value) > Date Then

to this

Code:
If DateValue(.Text) > Date Then

and see if resolves

Dave
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,995
Members
448,539
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top