Need help with date field on VBA userForm

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
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.
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, "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.
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,958
Other than adding an explicit DateValue function to your code (which I'm not sure why, but completness)
VBA Code:
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

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@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

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,958
This is why people use DatePickers or three comboboxes instead of textboxes to get a date entry.
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,961
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,405
Messages
5,624,574
Members
416,036
Latest member
eloisa manzanarez

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