instead of a general "date error", maybe the msgbox should tell them
what format you WANT it in.

MsgBox "Date Error - date must be in mm/dd/yyyy format." _
& vbcrlf & _
"Please try again using the correct format."

otherwise the user just keeps inputting the wrong format & it keeps
kicking it out.
susan


On Feb 26, 3:19 pm, "Aravind" wrote:
Thanks in Advance for help.

i created a User Form with data box that collects the date in the
format of MMDDYYYY, i tried to validate the date with lots of code but
in the end i failed because i am a novice to VBA. i was wondering if
there is anyway to take above format and actually spit out something
like this MM/DD/YYYY, at the same time have some kind of validation so
that if the user put's all zero's and wrong format we can tell the
user to reenter the date. This code was used but it has it's defects.

Private Sub DateTextBox_Exit(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo DateError
Newdate = Format(DateTextBox.Text, "00000000")
varDate = Left(Newdate, 2) & "/" & Mid(Newdate, 3, 2) & "/" &
Right(Newdate, 2)
DateTextBox.Text = DateValue(varDate)
Exit Sub
DateError:
MsgBox "Date Error"
DateTextBox.Text = ""
End Sub