Code For Date Accepts Text

Can someone tell me what is wrong here? Am asking the user to input a date
in the mm/dd/yy format. It works ok except that it accepts any text string
that is entered (it rejects something like January 3, 2006 though).
Obviously, I do not want it to accept text, and cannot figure out how to fix
this. Any help would be appreciated....am sure it is something simple.

Sub SWStartDate()
Dim UserEntry As String
Dim Msg As String
Msg = "Enter the contract start date, in mm/dd/yy format; then click on 'OK'."
Do
UserEntry = InputBox(Msg)
If UserEntry = "" Then
MsgBox ("You must enter a response; please try again.")
End If
If UserEntry <> "" Then
If UserEntry = Format(UserEntry, "mm/dd/yy") Then
Exit Do
End If
If UserEntry <> Format(UserEntry, "mm/dd/yy") Then
Msg = "Invalid entry; please enter a date in the mm/dd/yy
format."
End If
End If
Msg = "Invalid entry; please enter a date in the mm/dd/yy format."
Loop
Worksheets("Software Inventory").Range("C5").Value = UserEntry
End Sub

Hi Paige

Use the VBA IsDate function to test if Msg is a date
See VBA help for IsDate

--
Regards Ron De Bruin
http://www.rondebruin.nl



"Paige" <[email protected]> wrote in message news:[email protected]...
> Can someone tell me what is wrong here? Am asking the user to input a date
> in the mm/dd/yy format. It works ok except that it accepts any text string
> that is entered (it rejects something like January 3, 2006 though).
> Obviously, I do not want it to accept text, and cannot figure out how to fix
> this. Any help would be appreciated....am sure it is something simple.
>
> Sub SWStartDate()
> Dim UserEntry As String
> Dim Msg As String
> Msg = "Enter the contract start date, in mm/dd/yy format; then click on 'OK'."
> Do
> UserEntry = InputBox(Msg)
> If UserEntry = "" Then
> MsgBox ("You must enter a response; please try again.")
> End If
> If UserEntry <> "" Then
> If UserEntry = Format(UserEntry, "mm/dd/yy") Then
> Exit Do
> End If
> If UserEntry <> Format(UserEntry, "mm/dd/yy") Then
> Msg = "Invalid entry; please enter a date in the mm/dd/yy
> format."
> End If
> End If
> Msg = "Invalid entry; please enter a date in the mm/dd/yy format."
> Loop
> Worksheets("Software Inventory").Range("C5").Value = UserEntry
> End Sub
>

Works like a charm! Thanks so much Ron!

"Ron de Bruin" wrote:

> Hi Paige
>
> Use the VBA IsDate function to test if Msg is a date
> See VBA help for IsDate
>
> --
> Regards Ron De Bruin
> http://www.rondebruin.nl
>
>
>
> "Paige" <[email protected]> wrote in message news:[email protected]...
> > Can someone tell me what is wrong here? Am asking the user to input a date
> > in the mm/dd/yy format. It works ok except that it accepts any text string
> > that is entered (it rejects something like January 3, 2006 though).
> > Obviously, I do not want it to accept text, and cannot figure out how to fix
> > this. Any help would be appreciated....am sure it is something simple.
> >
> > Sub SWStartDate()
> > Dim UserEntry As String
> > Dim Msg As String
> > Msg = "Enter the contract start date, in mm/dd/yy format; then click on 'OK'."
> > Do
> > UserEntry = InputBox(Msg)
> > If UserEntry = "" Then
> > MsgBox ("You must enter a response; please try again.")
> > End If
> > If UserEntry <> "" Then
> > If UserEntry = Format(UserEntry, "mm/dd/yy") Then
> > Exit Do
> > End If
> > If UserEntry <> Format(UserEntry, "mm/dd/yy") Then
> > Msg = "Invalid entry; please enter a date in the mm/dd/yy
> > format."
> > End If
> > End If
> > Msg = "Invalid entry; please enter a date in the mm/dd/yy format."
> > Loop
> > Worksheets("Software Inventory").Range("C5").Value = UserEntry
> > End Sub
> >
>
>
>