Need help with date field on VBA userForm

[leopardhawk]

Hello forum friends, I have a problem with dates on a years-long project and I can't figure out a solution.

I have a VBA userForm where users will be entering dates in four textBoxes. I will focus only on one textBox in this plea for help and it's called DOBTextBox where the user enters their date of birth. Here is the VBA code for the DOBtextBox.

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, "mm/dd/yyyy")
        
        If (dt > Date) Or (Year(CDate(.Value)) < 1000) 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

If I type 'July 7 80' in the DOBTextBox and tab-key to the next field, the DOBTextBox displays '7/7/1980', and when the OK commandbutton is pressed, it sends the date to my worksheet into the appropriate cell which displays 'July 7, 1980 because I have formatted that cell to display the date as long-date (without the *).

However, if I type 'July 8 80' in the same DOBTextBox and tab-key to the next field, the DOBTextBox displays '8/7/1980', and when the OK commandbutton is pressed, it sends the date to my worksheet into the appropriate cell which now displays 'August 7, 1980' even though I typed July 8 80 in the textbox...!!!!! It is flipping the day and month for some reason.

I don't understand why it would do this, especially when I enter the date in the text box as an actual long date... Gotta have something to do with my VBA code but I have no idea how to address this problem. Any help appreciated. Thanks!

p.s. the third IF statement was added to prevent anyone from inadvertently entering the year as three digits. The code accepts 2-digit and 4-digit dates.

 

[mikerickson]

Other than adding an explicit DateValue function to your code (which I'm not sure why, but completness)

dt = Format(DateValue(.Value), "mm/dd/yyyy")

In the Good Date scenario, your Exit event is triggering the Change event, might there be something odd in there?

 

[leopardhawk]

@mikerickson hi Mike, thanks for the idea. I added the DateValue and it made no difference, the month and day are still flipping. Another date textbox on this userform uses an AfterUpdate event instead of an Exit event (at your suggestion, I might add) and I am having the same problem with that date (month and day being flipped)... It has to be something in the code...

On a whim, I changed the mm/dd/yyyy to dd/mm/yyyy and now when I enter 'jul 8 80' on the userForm and tab-key to the next field, I get 7/8/1980 in the DOBTextBox which is what I want... as long as the user enters a long-date in the textbox, everything will be fine but...

...this brings another problem out into the open. I tried just using numbers (4/12/80) or (12/4/80) and in both cases, as soon as I tab-key to the next field, the day and month flip! 4/12 becomes 12/4 and 12/4 becomes 4/12... So weird... what could cause this??

I'm sure hoping someone can help!!!

 

[mikerickson]

This is why people use DatePickers or three comboboxes instead of textboxes to get a date entry.

 

[leopardhawk]

Thanks Mike! I would LOVE to use a datepicker. My problem has always been trying to integrate one into a workbook that I am going to eventually make available to the general public. Not everyone has the Microsoft date picker in their copy of Excel. I did try to get one to work on my UserForm but eventually gave up because of too many errors. Maybe I'll have to consider the three combobox idea... ?

 

[Akuini]

Hi, @leopardhawk
My suggestion:
1. The user has to be clear what date format to insert, is it day-month-year or month-day-year?
Let's say it's day-month-year, then:

2. The user has to type exact date format in the textbox, say "##-##-####" ? and it means day-month-year such as 14-04-2020.
It will make it easier to get the right date when you send the value to the sheet.

If interested in this approach I think I can write the code.