User Form Text Date Format

[Glenn]

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

 

[Per Jessen]

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

 

[Glenn]

Many thanks Per, but unfortunately there is no change to my problem.

Glenn

 

[Mishell]

If you want to avoid any confusion when inputting dates in VBA or from a
Userform, (like the day being taken for the month and the month for the
day), always enter the date with the YYYY/MM/DD format.
That format is well interpreted by the Sheet and by VBA.

You may also use the computer's "Short Date" format. Even if it varies from
one computer to another, it is also always correctly interpreted.

TextBox1.Value = Format(TextBox1.Value, "Short date")

Range("A1").value = Format(TextBox1.Value, "Short date")


Mishell