Help with TextBox date Formatting

RJSIGKITS

Board Regular
Joined
Apr 15, 2013
Messages
109
Hi Forum.

I am really struggling with the date formatting of a textbox on a userform playing silly sods when transferring the data to a cell...
I need a dd/mm/yyyy format, however If I run the user form and add a date of 01/05/2014 to the textbox it applies to the cell as 05/01/2014, however, when I added 26/06/2014, it added correctly - any idea's?
Here's what I've got:
Code:
Private Sub TxtBxDate_Change()ThisWorkbook.Sheets("Supplier Order Log").Range("E3").Value = TxtBxDate.Value
End Sub

With
Code:
Private Sub UserForm_Initialize()TxtBxDate.Text = Format(Date, "dd/mm/yyyy")
End Sub

And my cell E3 on the "Supplier Order log" Sheet is formatted as Date, as dd/mm/yyyy

It appears that if the 'Day' number is smaller than 12, then it seems to assume that it is a month rather than a day - would that be what it's doing? I played around with a number of different dates using smaller days, but as soon as I add 13/12/2014 it formats correctly.
Also, I think I've just noticed that it keeps auto updating the date to todays date?.... Maybe not though, it might be doing something as I'm playing around with it....

Please help - any better solutions would be much appreciated.

Thanks in advance, guys!
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Code:
Private Sub TxtBxDate_Change()
ThisWorkbook.Sheets("Supplier Order Log").Range("E3").Value = DateValue(TxtBxDate.Value)
End Sub
 

RJSIGKITS

Board Regular
Joined
Apr 15, 2013
Messages
109
Just to note - I have resolved my original issue simply by formatting the cell as 'Text' rather than date so it simply reads the TextBox as it is.

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,108,702
Messages
5,524,410
Members
409,575
Latest member
navarrov74

This Week's Hot Topics

Top