Date format problem using Userform

Question

Hello

I need help regarding excel.

I create a userform where I add a text box named "txtName" for entering date. When I entered date like 04-01-17 (dd-mm-yy) on text box, it transfer on spreadsheet as 01/04/17 (mm-dd-yy).

I searched on google about the problem but cdate function not working for me.

Here is the full code:-

---

'Variable Declaration

Dim BlnVal As Boolean

Private Sub UserForm_Initialize()

    'Variable declaration

    Dim IdVal As Integer

    'Finding last row in the Data Sheet

    IdVal = fn_LastRow(Sheets("Data"))

    'Update next available id on the userform

    frmData.txtId = IdVal

End Sub

Sub cmdAdd_Click()

    On Error GoTo ErrOccured

    'Boolean Value

    BlnVal = 0

    'Data Validation

    Call Data_Validation

    'Check validation of all fields are completed are not

    If BlnVal = 0 Then Exit Sub

    'TurnOff screen updating

    With Application

        .ScreenUpdating = False

        .EnableEvents = False

    End With

    'Variable declaration

    Dim txtId, txtName, GenderValue, txtLocation, txtCNum, txtEAddr, txtRemarks

    Dim iCnt As Integer

    'find next available row to update data in the data worksheet

    iCnt = fn_LastRow(Sheets("Data")) + 1

    'Find Gender value

    If frmData.obMale = True Then

       GenderValue = "Male"

    Else

       GenderValue = "Female"

    End If

Worksheets("Data").Cells(2, 2).Value = CDate(Me.txtName)

    'Update userform data to the Data Worksheet

    With Sheets("Data")

        .Cells(iCnt, 1) = iCnt - 1

        .Cells(iCnt, 2) = frmData.txtName.Value

        .Cells(iCnt, 3) = GenderValue

        .Cells(iCnt, 4) = frmData.txtLocation.Value

        .Cells(iCnt, 5) = frmData.txtEAddr

        .Cells(iCnt, 6) = frmData.txtCNum

        .Cells(iCnt, 7) = frmData.txtRemarks

        'Diplay headers on the first row of Data Worksheet

        If .Range("A1") = "" Then

            .Cells(1, 1) = "Id"

            .Cells(1, 2) = "Name"

            .Cells(1, 3) = "Gender"

            .Cells(1, 4) = "Location"

            .Cells(1, 5) = "Email Addres"

            .Cells(1, 6) = "Contact Number"

            .Cells(1, 7) = "Remarks"

            'Formatiing Data

            .Columns("A:G").Columns.AutoFit

            .Range("A1:G1").Font.Bold = True

            .Range("A1:G1").LineStyle = xlDash

        End If

    End With

    'Display next available Id number on the Userform

    'Variable declaration

    Dim IdVal As Integer

    'Finding last row in the Data Sheet

    IdVal = fn_LastRow(Sheets("Data"))

    'Update next available id on the userform

    frmData.txtId = IdVal

ErrOccured:

    'TurnOn screen updating

    Application.ScreenUpdating = True

    Application.EnableEvents = True

End Sub

'In this example we are finding the last Row of specified Sheet

Function fn_LastRow(ByVal Sht As Worksheet)

    Dim lastRow As Long

    lastRow = Sht.Cells.SpecialCells(xlLastCell).Row

    lRow = Sht.Cells.SpecialCells(xlLastCell).Row

    Do While Application.CountA(Sht.Rows(lRow)) = 0 And lRow <> 1

        lRow = lRow - 1

    Loop

    fn_LastRow = lRow

End Function

'Exit from the Userform

Private Sub cmdCancel_Click()

    Unload Me

End Sub

' Check all the data(except remarks field) has entered are not on the userform

Sub Data_Validation()

     If txtName = "" Then

        MsgBox "Enter Name!", vbInformation, "Name"

        Exit Sub

     ElseIf frmData.obMale = False And frmData.obFMale = False Then

        MsgBox "Select Gender!", vbInformation, "Gender"

        Exit Sub

     ElseIf txtLocation = "" Then

        MsgBox "Enter Location!", vbInformation, "Location"

        Exit Sub

    ElseIf txtEAddr = "" Then

        MsgBox "Enter Address!", vbInformation, "Email Address"

        Exit Sub

    ElseIf txtCNum = "" Then

        MsgBox "Enter Contact Number!", vbInformation, "Contact Number"

        Exit Sub

    Else

        BlnVal = 1

    End If

End Sub

'Clearing data fields of userform

Private Sub cmdClear_Click()

    Application.ScreenUpdating = False

        txtId.Text = ""

        txtName.Text = ""

        obMale.Value = True

        txtLocation = ""

        txtEAddr = ""

        txtCNum = ""

        txtRemarks = ""

    Application.ScreenUpdating = True

End Sub

Please help me.. I hope you help me..

Answer 1

Your cell B2 has a little green triangle showing that the value entered is a string, which was done latter in your code by this line:

 .Cells(iCnt, 2) = frmData.txtName.Value

I should have been more specific - the code that I gave you needs to replace any code writing to the cell where you want the date, so take out the line above.

Answer 2

Thanks for your reply. I put your code. But it didn't work. If I write date on userform as 01-04-2017, then it add in worksheet as 01-04-2017. I want that When I will write date on userform as 01-04-17, the date will be add in worksheet as 01-Apr-17. How do I do that?

Dim v As Variant

v = Split(Me.txtName, "-")

With Worksheets("Data").Cells(iCnt, 2)

    .NumberFormat = "dd-mmm-yy"

    .Value = DateSerial(CInt(v(2)), CInt(v(1)), CInt(v(0)))

End With

After execute the above code, the date add on worksheet as 01-04-17 and shown a green arrow.

Answer 3

Try changing

 .NumberFormat = "dd-mm-yyyy"

to

 .NumberFormat = "dd-mmm-yyyy"

Answer 4

Thank you Bernie for help me. It works so fine.  If I write on userform date box as 01-04-2017, then it put on worksheet as 01-04-2017. And if I type in userform date box as 1-4-17, it put on worksheet as 1-4-17 not as 01-04-17.  Is there any way that I can type date in userform as 1-4-17 and it put on worksheet as 01-Apr-17.

Burnie what I think that could you do me a favor once more. I want to put the date in worksheet as 01-April-17 (dd-mmm-yy) while I want to type in userform date text box as 1-4-17. But how could I do that?

Thanks again. Please reply.

Answer 5

VBA defaults to mm/dd dates if the entry is valid in that form, so 08-11-17 will be August 11 and not November 8.

Try this, if you always enter your dates in dd-mm-yy form.

Dim v As Variant

v = Split(Me.txtName,"-")

With Worksheets("Data").Cells(2, 2)

    .NumberFormat = "dd-mm-yyyy"

    .Value = DateSerial(CInt(v(2)), CInt(v(1)), CInt(v(0)))

End With