Userform changing date format to American

[JamesT1]

Good Monring Folk

I have a userform that picks up a date, in UK format e.g 11/12/2013, from a cell when it opens using this code, this is how it should be

Please Login or Register  to view this content.

When the user puts additional information into the form and saves it the date is being changed to the American format 12/11/2013 in the new cell, the code for this part of the operation is

Please Login or Register  to view this content.

full code below, I can't understand why the format is being changed. Can someone please point out if and where I went wrong and why its changing the format as I need it to remain in the UK format

Please Login or Register  to view this content.

Many thanks for your help

[xlbiznes]

Hi,

Check your regional and language settings in the control panel of your computer. This might be set to United States.

[JamesT1]

HI xlbiznes

Thank you for your reply

Its not that,,, I also have dates for other things,, another userform in the same workbook also saves dates directly into a cell for me and this is OK... it is only this one form that is doing it and it is only this date, the other date "repdate" in the same form saves in the UK format. I think it has something to do with picking up a date from a cell and saving it to another cell as this is the only thing that is different from the rest.... I have checked the formatting in the destination cells and they are set from UK

Many thanks

[xlbiznes]

What happens when you put 13/12/2013 ?

[Stratfordoaks]

Put this in the Private Sub of the text box (below I have called it txt.date) you use to populate the cell in the worksheet:

Please Login or Register  to view this content.

[xlbiznes]

Hi,

i tried simulating your issue and this works fine on my pc.

Please Login or Register  to view this content.

[JamesT1]

Now I'm really confused... type directly into the destination cell and the format is fine.... use the form and put in todays or a previous date and it converts it to the American format however if I put in a future date it is in UK format

[xlbiznes]

You can try changing your code :

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

[JamesT1]

xlbiznes/Stratfordoaks

May thanks for your help guys,,,,, I've tried fomrating everything I can think of and used the advice from you but nothing will stop this changing to the US format in the cell....... Ive used a workround in the mean time,,, I needed it in UK format as another cell references the column for individual months,, i've changed this to reference days which gives me the same result for this format.....

many thanks

[Izandol]

You may use:

Please Login or Register  to view this content.

[xlbiznes]

Just posting what one of the forum guru @Norie has stated in an another thread.

Instead of Value use Text, then you'll get the value as it's formatted.

[Izandol]

The Text property may not be written - it may only be read.

[millz]

Or:

Please Login or Register  to view this content.

[Norie]

xlbzines

That's for getting a value from a cell as it's formatted.