UserForm Textbox accept only date format

[hajiali]

Private Sub CommandButton1_Click()
Dim emptyRow As Long

TextBox4.Value = Format(TextBox4.Value, "MM/DD/YYYY")
TextBox5.Value = Format(TextBox5.Value, "MM/DD/YYYY")

If TextBox1.Value = "" Or TextBox2.Value = "" Or TextBox3.Value = "" Or TextBox4.Value = "" Or TextBox5.Value = "" Or TextBox7.Value = "" Then
   MsgBox "TO ADD EMPLOYEE ALL FIELD WITH * MUSTH BE ENTERED", vbExclamation: Exit Sub
End If
    If Not IsNumeric(TextBox1) Then MsgBox "INVALID EMPLOYEE NUMBER ENTER NUMBER WITHOUT THE E", vbExclamation:
        TextBox1.SetFocus
    Exit Sub
        If Not IsDate(TextBox4) Then MsgBox "SLI DATE/DATE OF HIRE IS NOT INVALID DATE", vbExclamation:
            TextBox4.SetFocus
        Exit Sub
            If Not IsDate(TextBox5) Then MsgBox "DATE OF BIRTH IS NOT INVALID DATE", vbExclamation:
                TextBox5.SetFocus
            Exit Sub

With ActiveSheet
        emptyRow = WorksheetFunction.CountA(Range("B:B")) + 1
        Cells(emptyRow, 2).Value = TextBox1.Value
        Cells(emptyRow, 3).Value = TextBox2.Value
        Cells(emptyRow, 4).Value = TextBox3.Value
        Cells(emptyRow, 14).Value = TextBox4.Value
        Cells(emptyRow, 15).Value = TextBox5.Value
        Cells(emptyRow, 20).Value = TextBox6.Value
        Cells(emptyRow, 22).Value = TextBox7.Value
End With
End Sub

using the UserForm I'm trying to accomplish an error code if the value in textbox4 or textbox5 are not "MM/DD/YYYY" format to generate an error message. any help is greatly appreciate.

 

[Waimea]

Perhaps you could have 3 textboxes, one for MM, one for DD and lastly one for YYYY.

Then you could, for example, check if year is a 4 digit number?

Perhaps you could use comboboxes, one for year, one for month and one for day, then the user can only select the values you specify in the rowsource.

 

[hajiali]

Would their not be a way to fix the textbox to date format?

 

[eirikdaude]

Not really. You could try various error checking on users exiting or changing the contents of the textbox, but I'd bet my right hand someone would find a way to circumvent your error-checking. As usual for when people want to use dates in userforms (or in Excel in general) I'd recommend using a datepicker for it. There are a few you can find online if you search for it.

If you feel this is more effort than it's worth, some very simple error checking could be something like this:

Private Sub txtbox_Change()
    If Not IsDate(txtbox) Then
        txtbox.Clear
        MsgBox("You did not enter a valid date into the textbox")
    EndIf
End Sub

If you want your date to be in a certain format, I suppose there would be ways to check for that too, but it might be hard to protect against every eventuality.