Birthdate Textbox

[CokeOrCrack]

- 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:

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]

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]

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:

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

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.

 

[Smitty]

Why not just use a Date/Time Picker?

Here's one from Excel MVP Ron DeBruin: https://www.rondebruin.nl/win/addins/datepicker.htm

 

[CokeOrCrack]

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]

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]

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:

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

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:

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]

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.