UserForm Textbox accept only date format

hajiali

Active Member
Joined
Sep 8, 2018
Messages
386
Office Version
  1. 2016
Platform
  1. Windows
VBA Code:
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
1607289513977.png


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.
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Waimea

Active Member
Joined
Jun 30, 2018
Messages
423
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.
 
Last edited:

hajiali

Active Member
Joined
Sep 8, 2018
Messages
386
Office Version
  1. 2016
Platform
  1. Windows
Would their not be a way to fix the textbox to date format?
 

eirikdaude

Board Regular
Joined
Nov 26, 2013
Messages
60
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:

VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,653
Messages
5,626,105
Members
416,161
Latest member
David1966Lewis

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