Birthdate Textbox

CokeOrCrack

Board Regular
Joined
Dec 13, 2015
Messages
75
- Applicants input their birthdate in a textbox on a userform
- There are numerous formats and errors an individual can make when inputting a birthdate

Questions
- I want the textbox to only allow the input or paste of numbers [0-9] and slashes "/"
- I want the textbox to be formatted as m/d/yyyy
- Is there a way that if an applicant entered "09" the textbox would edit it to "9"?
- Is there a way for the textbox to require the input of two slashes?
- Is there a way to require either one or two values then "/", followed by one or two values then "/" followed by four values?
- Is there a way for dashes "-", periods "." and spaces to be changed to slashes "/"?

Notes
I currently have code that makes it so the textbox only accepts numbers and slashes upon typing, but an individual can still paste any value into the textbox:

Code:
Private Sub tbBirthdate_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
 Select Case KeyAscii
     Case Asc("0") To Asc("9")
     Case Asc("/")
     Case Else
         KeyAscii = 0
 End Select
 End Sub
Sorry for the multitude of questions, dates require several format checks.

Thanks

OJ
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
829
Office Version
2010
Platform
Windows
I'd suggest you use three combo boxes to reduce how often the user screws things up.
You can then form the date any way you want.
With the single text box you'll never know if they put the day or the month first. Did they mean March 4th or April 3rd ???
 

CokeOrCrack

Board Regular
Joined
Dec 13, 2015
Messages
75
NoSparks

Thanks for the response. I ran into another roadblock while trying to solve this. Applicants 18 and above are not required to input their birthdate, so I need to have an option in which the textbox can remain blank as well. I have checked off 3 of the 6 above questions I had and 1 is now obsolete. What I have come up with is as follows:

Code:
Private Sub tbBirthdate_Change()
    tbBirthdate.MaxLength = 10
    tbBirthdate.Text = Replace(tbBirthdate.Text, "-", "/")
    tbBirthdate.Text = Replace(tbBirthdate.Text, ".", "/")
    tbBirthdate.Text = Replace(tbBirthdate.Text, "01/", "1/")
    tbBirthdate.Text = Replace(tbBirthdate.Text, "02/", "2/")
    tbBirthdate.Text = Replace(tbBirthdate.Text, "03/", "3/")
    tbBirthdate.Text = Replace(tbBirthdate.Text, "04/", "4/")
    tbBirthdate.Text = Replace(tbBirthdate.Text, "05/", "5/")
    tbBirthdate.Text = Replace(tbBirthdate.Text, "06/", "6/")
    tbBirthdate.Text = Replace(tbBirthdate.Text, "07/", "7/")
    tbBirthdate.Text = Replace(tbBirthdate.Text, "08/", "8/")
    tbBirthdate.Text = Replace(tbBirthdate.Text, "09/", "9/")
End Sub
Code:
Private Sub tbBirthdate_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    Dim birth As String, i As Integer, newbirth As String
   
birth = Trim(Me.tbBirthdate.Value)
For i = 1 To Len(birth)
    If Mid(birth, i, 1) Like "[0-9]" Then
        newbirth = newbirth & Mid(birth, i, 1)
    ElseIf Mid(birth, i, 1) Like "/" Then
        newbirth = newbirth & Mid(birth, i, 1)
    End If
Next i
Me.tbBirthdate.Value = newbirth
End Sub
At this point, only the second (formatting as m/d/yyyy) and fifth (one or two values followed by slash, one or two values followed by slash, 4 values) are unsolved. I don't think the second can be solved. I am still attempting to solve the fifth.

Thanks again for the response.
 

CokeOrCrack

Board Regular
Joined
Dec 13, 2015
Messages
75
Smitty

I'm new to VBA (just started a week ago), so I was unaware that this was an option. A problem for my scenario is that the end users most often copy (from the application source) and paste (into the textbox). It would take more time for them to use the Date/Time Picker.

What I've noticed though is that the userform appears to be missing a right click menu. This will be something that I will need to add.
 

Smitty

MrExcel MVP
Joined
May 15, 2003
Messages
29,536
A problem for my scenario is that the end users most often copy (from the application source) and paste (into the textbox). It would take more time for them to use the Date/Time Picker.
I'm not sure of your process, but if you're making people go from sheet to form (and maybe back), you probably want to rethink it.

What I've noticed though is that the userform appears to be missing a right click menu. This will be something that I will need to add.
Right-click on a form isn't something you'd normally do, but you can.
 

CokeOrCrack

Board Regular
Joined
Dec 13, 2015
Messages
75
Smitty

Without naming names, the company is a little behind the times. This is also not for the applicants. The end users are myself and coworkers. We're taking applicant data and inputting it elsewhere (I understand, still not the most efficient way of doing it, but I'm trying my best).

I do believe I have solved all 6 of my original questions with the following:

Code:
Private Sub tbBirthdate_Change()
Dim birth As String, i As Integer, newbirth As String
    tbBirthdate.MaxLength = 10
    tbBirthdate.Text = Replace(tbBirthdate.Text, "-", "/")
    tbBirthdate.Text = Replace(tbBirthdate.Text, ".", "/")
    tbBirthdate.Text = Replace(tbBirthdate.Text, "//", "/")
    birth = Trim(Me.tbBirthdate.Value)
    For i = 1 To Len(birth)
    If Mid(birth, i, 1) Like "[0-9]" Then
        newbirth = newbirth & Mid(birth, i, 1)
    ElseIf Mid(birth, i, 1) Like "/" Then
        newbirth = newbirth & Mid(birth, i, 1)
    End If
    Next i
    Me.tbBirthdate.Value = newbirth
  
End Sub
Code:
Private Sub tbBirthdate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    tbBirthdate.Text = Format(tbBirthdate.Value, "m/d/yyyy")
End Sub
I have also included the following in the command button code to prompt an alert if what has been inputted is not a date:

Code:
Private Sub btnSubmit_Click()
If IsDate(tbBirthdate.Value) = False And tbBirthdate.Value <> vbNullString Then
        MsgBox "Invalid Birthdate"
        tbBirthdate.SetFocus
        Cancel = True
End If
I have intentionally given the option to leave the Birthdate textbox blank as applicants 18 and above are not required to input their age.

Hope this helps anybody that is looking to solve similar problems. Thanks to everyone for commenting and assisting.

OJ
 

Smitty

MrExcel MVP
Joined
May 15, 2003
Messages
29,536
Thinking on it a bit, if you're trying to get user input in a single workbook, you might want to look at Microsoft Forms.

It lets you build a survey type web form that feeds into a consolidated Excel workbook, and it does have a date field selection.
 

Forum statistics

Threads
1,081,849
Messages
5,361,673
Members
400,644
Latest member
ndroger1

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top