paste date from text box in form to cell

[ohexcell_ineedu]

Hey guys,

i am having some trouble pasting dates in the format dd/mm/yyyy to a cell. I have seen some solutions on the web but i was hoping for a quick fix solution, i can't believe that it should be a difficult task.

I have a form. I enter dates in the form in the format dd/mm/yyyy. Then when i paste the contents from the form to the cell it changes the format to mm/dd/yyyy.

Would someone please help me with this one. Are there some settings that i can change to stop this from happening.

You can download a xlsm file that does exactly as i describe from here https://dl.dropboxusercontent.com/u/9154762/Book1.xlsm

Thanks for any and all help.

 

[Special-K99]

Try formatting the cell as dd/mm/yyyy

 

[steve the fish]

Does this work?

= CDate(TextBox1.Text)

 

[Arithos]

Hey guys,

i am having some trouble pasting dates in the format dd/mm/yyyy to a cell. I have seen some solutions on the web but i was hoping for a quick fix solution, i can't believe that it should be a difficult task.

I have a form. I enter dates in the form in the format dd/mm/yyyy. Then when i paste the contents from the form to the cell it changes the format to mm/dd/yyyy.

Would someone please help me with this one. Are there some settings that i can change to stop this from happening.

You can download a xlsm file that does exactly as i describe from here https://dl.dropboxusercontent.com/u/9154762/Book1.xlsm

Thanks for any and all help.

Is your regional settings set to NA? DD/MM/YYYY is the EU "version of dates". You can change this in the windows regional settings. Else, just format the cells with custom formatting, should stick.

 

[ohexcell_ineedu]

Try formatting the cell as dd/mm/yyyy

Hey special-k99. it is formatted as dd/mm/yyyy. Its when i paste it from the form to the cell the dd and mm get switched for some reason.

 

[ohexcell_ineedu]

Does this work?

= CDate(TextBox1.Text)

Hey Steve, where do i put that? Does that belong in the vb code?

 

[ohexcell_ineedu]

Is your regional settings set to NA? DD/MM/YYYY is the EU "version of dates". You can change this in the windows regional settings. Else, just format the cells with custom formatting, should stick.

Hey Arithos, my regional settings are set to australia, which makes the system date dd.mm.yyyy. And custom formatting doesn't work, because it is literally switching the dd and mm values around unfortunately.

 

[ohexcell_ineedu]

Does this work?

= CDate(TextBox1.Text)

Actually i worked it out steve, and yep it worked exactly as i wanted it too!! thanks a million.

 

[steve the fish]

Or split the date:

Dim myDate As Variant
myDate = Split(Me.TextBox1.Value,"/") ' or "." if you use dots.
Range("A1").Value = DateSerial(myDate(2),myDate(1),myDate(0))

Yes into the commandbutton click code