Date Validation

Martel

New Member
Joined
Jun 19, 2019
Messages
25
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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,760
Office Version
  1. 365
Platform
  1. Windows
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
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,196
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Martel

New Member
Joined
Jun 19, 2019
Messages
25
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
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
1,760
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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"
 

Phuoc

Active Member
Joined
Apr 29, 2016
Messages
425
Office Version
  1. 2016
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
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,196
Office Version
  1. 2010
Platform
  1. Windows
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
 

Forum statistics

Threads
1,141,818
Messages
5,708,766
Members
421,588
Latest member
Wawie

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
Top