Date Validation

Martel

New Member
Joined
Jun 19, 2019
Messages
26
Hi. I want users to enter a date into a vba userform text box but with following conditions:

1. it must be entered as a 4 digit year
2. Prevent (trap) February 29th if "year" part of entry is not a leap year

I have searched without success for solutions. Obviously need to look at "year" to see if it meets usual leap year rules (divisible by 4 etc)
I cannot use "cdate" function because excel will not accept entry if not convertible to a valid date i.e. 29/02/2021 is not a valid date so cannot get past this point
Similarly cannot use "year" function on a non-valid date

I have found lots of suggestions for validating date input to ensure correct format (dd/mm/yyyy). also code to check for leap year, but nothing that combines the two - hope that is clear. Suggestions welcome
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Would something along these lines work for you ?
The isdate will cover off if its a valid date eg it will fail 29/02/2021 but accept 28/02/2021.
Once that is passed the CDate can do the conversion and check that the full year was in the initial date entered.

VBA Code:
    Dim inputStr As String
    Dim Yr As Long   
    
    inputStr = Application.InputBox(Prompt:="Test")
    If IsDate(inputStr) Then
        If InStr(inputStr, Year(CDate(inputStr))) > 0 Then
            MsgBox "Valid Date Entered"
        Else
            MsgBox "Invalid Date"
        End If
    End If
 
Upvote 0
If it were my project, and it isn't, I'd add a pop-up date picker such as the one by Trevor Eyre
then you would control the format of the date going into the text box.
 
Upvote 0
Would something along these lines work for you ?
The isdate will cover off if its a valid date eg it will fail 29/02/2021 but accept 28/02/2021.
Once that is passed the CDate can do the conversion and check that the full year was in the initial date entered.

VBA Code:
    Dim inputStr As String
    Dim Yr As Long  
   
    inputStr = Application.InputBox(Prompt:="Test")
    If IsDate(inputStr) Then
        If InStr(inputStr, Year(CDate(inputStr))) > 0 Then
            MsgBox "Valid Date Entered"
        Else
            MsgBox "Invalid Date"
        End If
    End If
Hi Alex and others

Thanks you Alex for your suggestion but I have not had any success with it. This may be a language problem, or I have not expressed my problem clearly or I am not understanding your response. Please bear with me..
1. I do not understand the expression "isdate will cover off ". sorry not come across this before - what does it mean?
2. Cannot make sense of your last line - I do not want to input 28/02/2021
3. I would expect 28/02/2021 to be accepted - (not a leap year) but asking for 29 days to be rejected
4. Text entered into Userform text boxes can be formatted to look like dates, but need Cdate function to convert them to actual dates - but Cdate will not work if Excel finds it cannot convert value entered into a date.
I see this as a 3 stage process viz:
Establish if year part of entry is a leap year e.g. 2020 but not 2021 - reject 2021
If leap year, 29 days in Feb is accepted otherwise reject
If not leap year, warn user and cancel entry
I would hope this could be achieved in Excel/VBA
I would be grateful if you could elaborate and give further explanation of your suggestion
 
Upvote 0
If you get to the line below the Date is valid according to your criteria and you can replace the line with whatever you want to do if it is valid
MsgBox "Valid Date Entered"
If you get to the line below then the date is invalid and you need to warn the use and cancel the entry
MsgBox "Invalid Date"
 
Upvote 0
Try this:

Code:
Private Sub TextBox1_AfterUpdate()
Dim txt As String, a As Variant, d As Date
txt = Me.TextBox1.Value
    a = Split(txt, "/")
    d = DateSerial(a(2), a(1), a(0))
    If Month(d) <> CLng(a(1)) Then
        MsgBox "Invalid Date"
        Me.TextBox1.Value = ""
    End If

End Sub
 
Upvote 0
possibly
VBA Code:
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Me.TextBox1.Text <> "" Then
        With Me.TextBox1
            If IsDate(.Text) Then
                .Text = Format(.Text, "dd/mm/yyyy")
            Else
                Cancel = True
                MsgBox "Invalid Date"
                .SelStart = 0
                .SelLength = Len(.Text)
            End If
        End With
    End If
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,217
Members
448,554
Latest member
Gleisner2

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