Issue with Userform READING dates as USA

[AnvarJay]

Hi everyone, I have searched for help in many forums etc and all look and sound right but none are working for me.

As you can see my Spreadsheet is in dd/mm/yy but as my userform reads the data it changed it to mm/dd/yy and i set a msgbox to show also incase the value of the item changed again. I can get it to right the data as dd/mm/yy but when i then go back in again the spreadsheet is correct at dd/mm/yy but the userform again reads it as mm/dd/yy and i must again manually change it even if i dont need to edit that field.

Thats the code for pulling up the form to show what is already in the sheet, except it chaged to mm/dd/yy. Also my local settings are all as Australian, and set to local - *dd/mm/yy settings.

Does anyone have some suggestions. This is just a play with file to get the dates working as all of the rest of the real sheet is fine and doesnt do anything to the dates anyway. So this sample will be all i need to get right to then put it in my larger file code.
Thanks

 

[dmt32]

Instead of using the Value of the textbox, have you tried using Text?
= Format(Me.TxtAccepted.Text,"dd/mm/yy")

I suggested use of Range.Text property in #post 4 - not sure why OP has not understood it.

Dave

 

[AnvarJay]

Instead of using the Value of the textbox, have you tried using Text?
= Format(Me.TxtAccepted.Text,"dd/mm/yy")

no i hadnt, but just did and still did it in USA but thanks for asking

 

[AnvarJay]

I suggested use of Range.Text property in #post 4 - not sure why OP has not understood it.

Dave

honestly didnt try range, as by my research (of which is limited) and so forth range seems to only let me use set cells, eg A4 but i need it to point to a varying row but set column so i havent tried further yet.

 

[dmt32]

honestly didnt try range, as by my research (of which is limited) and so forth range seems to only let me use set cells, eg A4 but i need it to point to a varying row but set column so i havent tried further yet.

I provided an example what to change in your code

all you should need to do is replace in your code Value shown in bold

Me.TxtCustomer.Value = ws.Cells(ActiveCell.Row, 1).Value

with Text shown in bold

Me.TxtCustomer.Value = ws.Cells(ActiveCell.Row, 1).Text

Your date formats should then be correctly displayed in your textbox

Dave

 

[AnvarJay]

I provided an example what to change in your code

all you should need to do is replace in your code Value shown in bold

Me.TxtCustomer.Value = ws.Cells(ActiveCell.Row, 1).Value

with Text shown in bold

Me.TxtCustomer.Value = ws.Cells(ActiveCell.Row, 1).Text

Your date formats should then be correctly displayed in your textbox

Dave

sorry DMT no offense, but every answer u have given me ONLY refers to the customer txt box and as I have indicated that box is fine, it is the date box ones that are an issue. I will try it but as mentioned earlier i tried the date ones as text also and didnt work. They are text boxes that read into a dateformat field and column so not sure what will happen. You need to be a lot clearer on what u are explaining and use the correct fields in question if you are going to give an answer (like what Dermot does) before you have a dig saying i dont listen.

 

[AnvarJay]

I provided an example what to change in your code

all you should need to do is replace in your code Value shown in bold

Me.TxtCustomer.Value = ws.Cells(ActiveCell.Row, 1).Value

with Text shown in bold

Me.TxtCustomer.Value = ws.Cells(ActiveCell.Row, 1).Text

Your date formats should then be correctly displayed in your textbox

Dave

ok dmt i just tried to replace value with text on ALL fields that are dates (ignore customer field it is fine) and it did NOT work. I tried it on the read in code and on the write to code. I have all fields that need to be dates (which is ALL except customer) set as DIM TxtFieldname As Date. I have formatted in many different places and isnt working. If you can assist with the date fields it would be greatly appreciated.

 

[AnvarJay]

For all in case this helps. Global Decs

and remainder of code for form and what it does.

So nice and simple, enter a cust name, and 4 dates that dont matter for anything but MUST be read in as dates and be written to ws as dates. They at the moment is all that is important, as they need to be dates to work for what i need as filters within the pivot tables. In the real version of this with all the extra data, everything else is perfect for what i need except dates reading in and writing out, hence why i made a small sample version with mainly just dates to get it right for the big version.

 

[AnvarJay]

Finally worked it out, thanks everyone but found it myself from combo of diff info from here and other sites. Honestly cant belive the dramas that MS give those who are not usa citizens, it really is stupid but for anyone else looking here it is