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

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

You may find your job as a programmer gets simpler if you use a calendar control
to get the date.

Ron de Bruin has some tips/links at:
http://www.rondebruin.nl/calendar.htm

And you wouldn't have to take a chance that 02032007 meant Feb 3rd, 2007 or
March 2nd, 2007.



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

--

Dave Peterson