Userform Date always reverts to American format

[Henrybukowski]

Dear all,
I have a problem with american date formats always reoccuring. I have seen other posts on this problem but none matching my particular issue.

I have a userform in which my users enter data which is then added to the excel spreadsheet.
Sometimes they need to amend this data so we have made their data editable - it gets put back into the userform so that changes can be made.

The issue is with dates. These are entered as dd/mm/yyyy but when they are then added to the userform they always go back to mm/dd/yyyy

The code to put the userfrom data to the spreadsheet is as follows:


<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">

ws.Cells(iRow, 28).Value = Me.refdate.Value </code>[/COLOR][COLOR=#333333][FONT=monospace]

[/FONT]
...Then to put it back into the form for editing:

[/FONT][/COLOR][COLOR=#333333]<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">.refdate.Value = Cells(ActiveCell.Row, "AB").Value </code>[/COLOR][COLOR=#333333][FONT=monospace]

I made some progress by specifying instead of the first line:

[/FONT][/COLOR][COLOR=#333333]<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">ws.Cells(iRow, 75).Value = DateValue(Me.phdat.Value)</code>[/COLOR][COLOR=#333333][FONT=monospace]

Using DataValue or CDate. This works to an extent because data is now added in the format dd/mm/yyyy - hooray!

However, when put back into the userform it still goes back in the form mm/dd/yyyy!!!!

Is there a way I can override this American date format which keeps coming back?

Many thanks in advance

Henry

NB: Linked: http://www.excelforum.com/excel-programming-vba-macros/1149872-date-always-reverts-to-american-format-in-userform.html

 

[RoryA]

What is refdate? A Textbox or something else?

 

[Henrybukowski]

What is refdate? A Textbox or something else?

Apologies @RoryA I should have said, but yes your right: It's short for referral date and it's just a text box (rather than a combo) to allow for dates

 

[RoryA]

If it's just a textbox then it should show the cell contents as they are. If you are seeing the data with day/month swapped, that would suggest the date in the cell is actually swapped but perhaps formatted to display the way you expect.

 

[Henrybukowski]

Rory, thanks for you reply but not sure I follow.
The cells are formatted to Date as well if that's what you mean?

If it helps when I tried working with 'DateValue' I now get a particular error which is Run-time Error 13: Type: Mismatch.

It then highlights yellow ' ws.Cells(iRow, 28).Value = DateValue(Me.refdate.Text) '
Hmmm

 

[RoryA]

That would imply that whatever is in the refdate control can't be converted to a date using your regional settings.