Force TextBox to be date format

[thorpyuk]

Hiya,

I'd like to import some dates into some textboxes on a userform.

I'm also going to let the user edit these, however... what i'd like is for the user to be forced to:

1) enter a date in the format dd/mm/yy
2) Enter a valid date (eg, not feb 30th)


Please can anyone help?

Thanks

 

[Jotoya]

Hi Andrew,

What portion of the code would I have to change so that it will work with U.S. based format (mm/dd/yyy)? Thank you.

 

[Ark68]

Hi all,

I'm trying to apply Thoryuk's code from post#10 to my userform but there seems to be a problem. The mask works fine, allowing users to only enter data in the ##/##/#### format.

When the form is first executed, it accepts dates properly. However, if I delete that date, and enter another, any dates entered following the initial trigger the error notice. It will not accept any other date, proper or other, after the first one.

Now for the ultimate of stupid questions, the basics of forms, ... I want to put that date value in cell A1. What is the code, and where in this would I put it?

 

[sujon]

Helpful to me

 

[spycein]

Hi Andrew tried what you mentioned but still having the problem, i input data as dd/mm/yy format in userform but it saves as mm/dd/yy format in excel. i am using excel 2013.
For example if i enter 07/03/14 in user form, the value gets changed in excel as 03/07/14.

Please help..

Thanks Andrew, i've changed it to: (which seems to work)

Private Sub TextBox2_Change()
    Dim Char As String
    Dim x As Date
    Dim y As Date
    Char = Right(TextBox2.Text, 1)
    Select Case Len(TextBox2.Text)
    Case 1 To 2, 4 To 5, 7 To 10
        If Char Like "#" Then
            If Len(TextBox2) = 10 Then
                On Error Resume Next
                x = DateValue(TextBox2.Text)
                y = DateSerial(Right(TextBox2, 4), Mid(TextBox2, 4, 2), Left(TextBox2, 2))
                If Err = 0 And x = y Then
                    On Error GoTo 0
                    Exit Sub
                Else
                    Err.Clear
                    On Error GoTo 0
                    TextBox2.SelStart = 0
                    TextBox2.SelLength = Len(TextBox2.Text)
                    MsgBox "Please enter a valid date in the form dd/mm/yyyy", vbCritical + vbOKOnly, "Error"
                    Exit Sub
                End If
            Else
                Exit Sub
            End If
        End If
    Case 3, 6
        If Char Like "/" Then Exit Sub
    End Select
    'Beep
    On Error Resume Next
    TextBox2.Text = Left(TextBox2.Text, Len(TextBox2.Text) - 1)
    TextBox2.SelStart = Len(TextBox2.Text)
End Sub

 

[Andrew Poulsom]

Welcome to MrExcel.

What code are you using to transfer the date to a cell? It's best to use the DateValue function, which is locale aware, eg:

Range("A1").Value = DateValue(TextBox2.Text)