Check Date Entry in TextBox on UserForm

How to check for a date entry in a text-box on a user-form with a macro and also calculate the age of the user. Watch the video below:

Check if date has been entered in textbox on user-form

First we create a user-form with a label, text-box and command button as shown below:

UserForm with Label, TextBox and Command Button

We set the font size of the label to 14 and bold and align the text to the center. We give the command button the caption ‘calculate age’.

Next we double click on the command button to get these two lines of code:

Private Sub CommandButton1_Click()

End Sub

Between these two lines of code we write the following macro code to get:

Private Sub CommandButton1_Click()

If IsDate(TextBox1.Value) Then
MsgBox “Your age (in years) is ” & (Year(Date) – Year(TextBox1.Value))
Range(“B2”) = (Year(Date) – Year(TextBox1.Value))
Else
MsgBox “Date not entered. ” & TextBox1.Value & ” entered!”
If Len(TextBox1.Text) <> 4 Then
MsgBox “Please enter a 4 digit number for year!”
Exit Sub
End If
MsgBox “Your age (in years) is ” & (Year(Date) – Val(TextBox1.Value))
Range(“C2”) = (Year(Date) – Val(TextBox1.Value))

End If
End Sub

IsDate checks whether a date has been entered into the TextBox1 by the user. If yes, we subtract the ‘Year’ value of the date from the current date of our system. This gives us the age in years:

Note: Enter the date of birth as mm-dd-yyyy or dd-mm-yyyy

(Year(Date) – Year(TextBox1.Value))

If the user enters a numerical value like 1990 or 2000 (representing a year) into the text-box, we convert it into a numerical value using ‘val’ because numbers entered into the textbox are treated as text:

Val(TextBox1.Value))

This value is subtracted from the ‘Year’ value of the current system date and we get the age in years:

(Year(Date) – Val(TextBox1.Value))

We can display the age using a message box or we can transfer the value to the appropriate worksheet range or cell using:

Range(“C2”) = (Year(Date) – Val(TextBox1.Value))

Further Reading:

Create Custom Dialog Boxes