Date formatting issue in user form

[Jennifer]

Well for umpteenth time I have a question,
I would like the user to be able to type 041505 hit enter or tab and in the
user form now show the date as o4-15-05.
The following is what I have attempter thus far. Thank you!
Private Sub txtDate_Enter()
txtDate.Value = Format(Me.txtDate, "")
End Sub
Private Sub txtDate_Exit(ByVal cancel As MSForms.ReturnBoolean)
txtDate.Value = Format(Me.txtDate.Value, "dd/mmm/yy")
End Sub
--
Though daily learning, I LOVE EXCEL!
Jennifer

[William]

Hi Jennifer

You may need some additional error checking etc, but this may help

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Len(TextBox1) <> 6 Then
MsgBox "6 numbers required in ''mmddyy'' format please"
Exit Sub
End If
TextBox1 = Left(TextBox1, 2) & "-" & Mid(TextBox1, 3, 2) & "-" &
Right(TextBox1, 2)
End Sub

--

-----
XL2003
Regards

William

[email protected]


"Jennifer" <[email protected]> wrote in message
news:[email protected]...
> Well for umpteenth time I have a question,
> I would like the user to be able to type 041505 hit enter or tab and in
> the
> user form now show the date as o4-15-05.
> The following is what I have attempter thus far. Thank you!
> Private Sub txtDate_Enter()
> txtDate.Value = Format(Me.txtDate, "")
> End Sub
> Private Sub txtDate_Exit(ByVal cancel As MSForms.ReturnBoolean)
> txtDate.Value = Format(Me.txtDate.Value, "dd/mmm/yy")
> End Sub
> --
> Though daily learning, I LOVE EXCEL!
> Jennifer

[Fadi Chalouhi]

Hi Jennifer,

try this. Private Sub txtDate_Exit(ByVal cancel As
MSForms.ReturnBoolean)
txtDate.Value = Format( date("20" &
RIGHT(Me.txtDate.Value,2),LEFT(Me.txtDate.Value,2),MID(Me.txtDate.Value,3,2)),
"dd/mmm/yy")
End Sub

be careful on reformatting the textbox when you re-enter it.

HTH

Fadi
www.chalouhis.com/XLBLOG

[Bob Phillips]

Take a look at http://www.xldynamic.com/source/xld.QDEDownload.html

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jennifer" <[email protected]> wrote in message
news:[email protected]...
> Well for umpteenth time I have a question,
> I would like the user to be able to type 041505 hit enter or tab and in
the
> user form now show the date as o4-15-05.
> The following is what I have attempter thus far. Thank you!
> Private Sub txtDate_Enter()
> txtDate.Value = Format(Me.txtDate, "")
> End Sub
> Private Sub txtDate_Exit(ByVal cancel As MSForms.ReturnBoolean)
> txtDate.Value = Format(Me.txtDate.Value, "dd/mmm/yy")
> End Sub
> --
> Though daily learning, I LOVE EXCEL!
> Jennifer