VBA code accepting date (yyyy) as three digits?!

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
Hello forum friends, 🤕 me after banging my head against the wall!

What is it with Excel and dates???

I have this VBA code for a textbox on a Userform where the user enters their date of birth and the code is supposed to prevent errors in the textbox. I thought everything was working great until I accidentally typed July 2, 194 inadvertently omitting the final digit which was supposed to be a '9' (1949). When I tabbed away to the next textbox, I noticed that the DOB textbox had 7/2/194 in it which it shouldn't because of the code plus, it will be sent to the worksheet when the user selects the OK commandbutton which will create another suite of problems. There was no MsgBox either advising me to "Please enter the year as four digits.".

I am hopeful that someone can help me sort this out, why is it accepting a three-digit year??? Thanks!

VBA Code:
Private Sub DOBTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim dt As Date, tx As String
With DOBTextBox
If .Value = "" Then Exit Sub
    If IsDate(.Value) Then
        tx = .Value
        dt = Format(.Value, "d/m/yyyy")
        
        If dt > Date Then
            MsgBox "Please enter the year as four digits."
            Cancel = True
        Else
            .Value = dt
        
        End If
    
    Else
        MsgBox "Please enter a valid date!"
        Cancel = True
        .Value = Empty
    End If
End With

End Sub
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
920
Office Version
  1. 2016
Platform
  1. Windows
I guess during dinosaur time the year is in single digit. So, still valid for 3 digit 😁

Maybe you can test the text in textbox like
=MID(.Text,LEN(.Text)-4,1)

If detected "/' then ok
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,950
VBA Code:
If (dt > Date) or (Year(CDate(.Value))<1000) Then

What is it with Excel and dates???

That question has been asked many times. Often using words shorter than the ones you used. Because of Excel's peculiarities in handling dates, it's common for dates to be entered with 3 combo boxes (day, month, year) rather than a single text box.
 
Solution

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@mikerickson Where would I put your line of code within mine? I making the assumption that you are suggesting this as something to add to my code but I could be wrong. Thanks!

@Zot I'm a bit of a noob so I would need a bit more guidance on your suggestion as well. Thanks!
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
920
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Maybe something like this. Detecting if it is " / " counting from right of text entered.

VBA Code:
Private Sub DOBTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim dt As Date, tx As String
With DOBTextBox
If .Value = "" Then Exit Sub
If Not Mid(.Text, Len(.Text) - 4, 1) = "/" Then
    MsgBox "Year is not 4 digit", vbCritical
    Exit Sub
End If
    If IsDate(.Value) Then
        tx = .Value
        dt = Format(.Value, "d/m/yyyy")
        
        If dt > Date Then
            MsgBox "Please enter the year as four digits."
            Cancel = True
        Else
            .Value = dt
        
        End If
    
    Else
        MsgBox "Please enter a valid date!"
        Cancel = True
        .Value = Empty
    End If
End With

End Sub
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@Zot I'm getting the MsgBox "Year is not 4 digit" even when I type in a 4-digit year.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
920
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

@Zot I'm getting the MsgBox "Year is not 4 digit" even when I type in a 4-digit year.
I tried
1) If Not Mid(.Text, Len(.Text) - 4, 1) = "/" Then
2) If Not Mid(.Value, Len(.Value) - 4, 1) = "/" Then
3) If Not Mid(Cstr(.Text), Len(Cstr(.Text)) - 4, 1) = "/" Then

they were all skipping the msg. I put 3 digit year and got the msg

Probably you have space after year, accidentally? Can put trim as safety
If Not Mid(Trim(.Value), Len(Trim(.Value)) - 4, 1) = "/" Then

Maybe can put breakpoint (F9) on thet line and check what value it is reading to debug?

I have no idea why
 

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,261
Office Version
  1. 2010
Platform
  1. Windows
i am a fan of stopping users from entering erroneous info in the first place. mikericksons suggestion is by far the best way of removing the issue, and the error trapping problems you are getting.
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@diddi @mikerickson okay, I think I figured it out. I put Mike's line of code in place of "If dt > Date Then" in my code. Seems to be working as it should.
Thanks guys!
 

Watch MrExcel Video

Forum statistics

Threads
1,126,983
Messages
5,621,966
Members
415,869
Latest member
LWSkinner

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