Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form Text Date Format
I am setting up a User Form to enter data. Most of the Combo Boxes work very
well but the 2 date boxes are challenging me! I had thought I could format the User Form through Properties but that appears not the case. I have tried a couple of suggestions I have seen in the forum: Private Sub TextBox4_Change() TextBox4.Value = Format(TextBox4.Value, "d mmm yy") End Sub Private Sub TextBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean) TextBox4.Value = Format(TextBox4.Value, "d mmm yy") End Sub The first example pre-fills the User Form box with 31 Dec 99 no matter what key I touch. The second example behaves as folows: I enter 2 Mar 10 and get 2 Mar 10 in the Sheet but get 3 Feb 10 in the User Form. I enter 2 Dec 09 and get 2 Dec 09 in the Sheet but get 3 Feb 10 in the User Form. I enter 02/09/09 and get 9 Feb 09 in the Sheet but correctly 2 Sep 09 in the User Form. I wonder if anyone can shed some light on where I may be going wrong. Many thanks. Glenn |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form Text Date Format
Hi Glenn
Use 'CDate' to convert the data to a true date before formatting it: Private Sub TextBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean) TextBox4.Value = Format(CDate(TextBox4.Value), "d mmm yy") End Sub Hopes this helps. .... Per On 2 Mar., 21:43, Glenn wrote: I am setting up a User Form to enter data. *Most of the Combo Boxes work very well but the 2 date boxes are challenging me! I had thought I could format the User Form through Properties but that appears not the case. *I have tried a couple of suggestions I have seen in the forum: Private Sub TextBox4_Change() TextBox4.Value = Format(TextBox4.Value, "d mmm yy") End Sub Private Sub TextBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean) TextBox4.Value = Format(TextBox4.Value, "d mmm yy") End Sub The first example pre-fills the User Form box with 31 Dec 99 no matter what key I touch. The second example behaves as folows: I enter 2 Mar 10 and get 2 Mar 10 in the Sheet but get 3 Feb 10 in the User Form. I enter 2 Dec 09 and get 2 Dec 09 in the Sheet but get 3 Feb 10 in the User Form. I enter 02/09/09 and get 9 Feb 09 in the Sheet but correctly 2 Sep 09 in the User Form. I wonder if anyone can shed some light on where I may be going wrong. *Many thanks. Glenn |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form Text Date Format
Many thanks Per, but unfortunately there is no change to my problem.
Glenn "Per Jessen" wrote: Hi Glenn Use 'CDate' to convert the data to a true date before formatting it: Private Sub TextBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean) TextBox4.Value = Format(CDate(TextBox4.Value), "d mmm yy") End Sub Hopes this helps. .... Per On 2 Mar., 21:43, Glenn wrote: I am setting up a User Form to enter data. Most of the Combo Boxes work very well but the 2 date boxes are challenging me! I had thought I could format the User Form through Properties but that appears not the case. I have tried a couple of suggestions I have seen in the forum: Private Sub TextBox4_Change() TextBox4.Value = Format(TextBox4.Value, "d mmm yy") End Sub Private Sub TextBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean) TextBox4.Value = Format(TextBox4.Value, "d mmm yy") End Sub The first example pre-fills the User Form box with 31 Dec 99 no matter what key I touch. The second example behaves as folows: I enter 2 Mar 10 and get 2 Mar 10 in the Sheet but get 3 Feb 10 in the User Form. I enter 2 Dec 09 and get 2 Dec 09 in the Sheet but get 3 Feb 10 in the User Form. I enter 02/09/09 and get 9 Feb 09 in the Sheet but correctly 2 Sep 09 in the User Form. I wonder if anyone can shed some light on where I may be going wrong. Many thanks. Glenn . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
International date format and user form | Excel Programming | |||
Date format changes when inputed from user form | Excel Discussion (Misc queries) | |||
visual basic user form date format dd/mm/yy not mm/dd/yy | Excel Discussion (Misc queries) | |||
date format in user form | Excel Programming | |||
how to format a date/validate for a text box entry on a user form | Excel Programming |