Date from Userform not transferring properly

Question

I have a userform in Excel 2013 and one of the text boxes allows the user to enter a date in Australia formation DD/MM/YYYY, and when the user clicks the Add button, it transfers it into the next available row on a worksheet.

The problem I am having is if the user enters a date for the 1st august 2012, 01/08/2013 it is adding it to the worksheet as 08/01/2013, if I put a date in for the 22nd august 2013, it enters it correctly - 08/22/2013. If i put the date in as 08/01/2013 on the userform it adds it as 01/08/2013 on the worksheet.

the two lines of code I am using is:- Worksheets("Data_Debt").Range("A" & Drow).NumberFormat = "dd/mm/yyyy" Worksheets("Data_Debt").Range("A" & Drow).Value = UserForm4.TextBox1.Value

Can anyone give me atip on what I am doing wrong.

BTW. I have the columns in the worksheet set to a ShortDate format.

Thanks in advance

Keith

Accepting dates in textboxes usually creates this problem. Perhaps [THIS](http://stackoverflow.com/questions/12012206/formatting-mm-dd-yyyy-dates-in-textbox-in-vba) will solve your problem? — Siddharth Rout, Oct 24 '13 at 12:13
echo sid's comments, I'd probably use 3 text boxes or even you could use comboboxes which would allow you to better control the inputs (by limiting to an allowable list of values) and ensure they're always numeric type, etc. — David Zemens, Oct 24 '13 at 12:36

score 0 · Answer 1 · answered Oct 24 '13 at 12:27

Get the date as a String and then use the DateSerial() function:

Sub DepositDate()
    Dim sD As String, D As Date
    sD = Application.InputBox(Prompt:="Please enter date as dd/mm/yyyy", Type:=2)
    ary = Split(sD, "/")
    Range("A1").Value = DateSerial(ary(2), ary(1), ary(0))
    Range("A1").NumberFormat = "dd/mm/yyyy"
End Sub

Date from Userform not transferring properly

1 Answers1