Dates and formatting TextBoxes in a Userform

you should be careful when using this format within VBA, VBA will first try and convert a date into american standard format, if you dont explicitly convert the dates yourself between mm/dd/yyyy and dd/mm/yyyy your going to find that your data is going to get corrupted.

always set the cell to a text format.... this way you know excel has made any changes to it, before you use it in VBA convert it into american format first, then do whatever you need to do and then convert it back to UK standard format (dd/mm/yyyy) before you display it again.

John

How are you putting the date in the textbox?

John

Where and when is the date appearing with the wrong format?

Using Format when populating the textbox should work but when putting the value from the textbox in a cell you should be converting it to a 'real' date using DateValue or CDate and setting the format of the cell to what you require.

just be careful with using Format(Date,"dd/mm/yyyy"), if you put this result directly into a cell its likely going to mix up the dd/mm part depending on the date you use. norie's suggestion is good but you must convert it into the correct type first.....

either enter into the sheet as a mm/dd/yyyy format first and let the formatting handle the conversion into dd/mm/yyyy

or you can convert it into the format excel uses internally with CDbl(CDate("10/10/2016 10:30PM")) this will give you a number that excel will always understand as the correct date you just need to add a cell format to format it how you want it, but even this method is not reliable..

just to show you how weird this gets run this code

Please Login or Register  to view this content.

this is what i got
42653.9375
42668.9375
42668.9375
10/10/2016 10:30:00 PM
10/25/2016 10:30:00 PM <- as you can see CDate changes the UK format date into US format
10/25/2016 10:30:00 PM

since your not doing calculations on the date just set the cells to text and just assume whatever text is in the textbox is the right format....

Just to add, to this for anyone looking into this problem.......

The particular problem i point out here happens when your trying to mix 2 different date styles/formats within a single localization

obviously the localization in the UK for example will always format the date (In Excel) in the UK format and assume you mean this format, my example above is using the US localization so it assumes US standards.......

i havent tested this with VBA internally, im not sure right now if it uses localization, but if your using excel between 2 locations using their different localization settings the dates in excel normally would be fine to work with, the problem may come with sharing a file using VBA handling dates and especially accepting date inputs from a user (is it the UK user or the US user entering this date?)