Input Box Asking for Date, and Validating Format
 

For the life of me, I cannot get this to work; either it keeps looping,
doesn't return the right message, or bombs out. All I'm trying to do is ask
the user to input a date in mm/dd/yy format.
1) If they hit cancel or don't enter anything, then they should get a
message saying they must enter a response.
2) If they enter something but it is not in the mm/dd/yy format, they should
get a message that it is not in the correct format.
3) If they enter it correctly, their response should be put into cell A1.

Can someone PLEASE help me? What I have is:
Sub GetDate()
Dim Response As String
Dim Msg As String
Msg = "Enter the contract start date, in mm/dd/yy format; then click on 'OK'."
Do
Response = InputBox(prompt:=Msg)
If Response = "" Then
MsgBox ("You must enter a response; please try again.")
End If
If Response < Format(CDate(Response), "mm/dd/yy") Then
MsgBox ("Enter a valid date in mm/dd/yy format; please try
again.")
End If
Loop
Range("A1").Value = Response
End Sub

Input Box Asking for Date, and Validating Format
 

Hi Paige

Why not use this
http://www.rondebruin.nl/calendar.htm

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


"Paige" wrote in message ...
For the life of me, I cannot get this to work; either it keeps looping,
doesn't return the right message, or bombs out. All I'm trying to do is ask
the user to input a date in mm/dd/yy format.
1) If they hit cancel or don't enter anything, then they should get a
message saying they must enter a response.
2) If they enter something but it is not in the mm/dd/yy format, they should
get a message that it is not in the correct format.
3) If they enter it correctly, their response should be put into cell A1.

Can someone PLEASE help me? What I have is:
Sub GetDate()
Dim Response As String
Dim Msg As String
Msg = "Enter the contract start date, in mm/dd/yy format; then click on 'OK'."
Do
Response = InputBox(prompt:=Msg)
If Response = "" Then
MsgBox ("You must enter a response; please try again.")
End If
If Response < Format(CDate(Response), "mm/dd/yy") Then
MsgBox ("Enter a valid date in mm/dd/yy format; please try
again.")
End If
Loop
Range("A1").Value = Response
End Sub

Input Box Asking for Date, and Validating Format
 

You shouldn't require a spesific FORMAT, that's lazy programming, nothing
less. I mean, if a user enters a perfectly valid date in mm/dd/yyyy format
or mmmm d yy format, why on earth should a piece of software reject it and
still expect some respect ? Also, you leave the user no way to cancel the
opertation (which usually returns ""). Please run this code:

Sub Respect()
Dim i As Long
For i = 1 To 20
MsgBox "I won't do it again.", , "Sorry"
Next
End Sub

Now try this, it accepts all common date formats and adjusts to the local
regional settings (mm/dd vs dd/mm). No year (like "Jun 1") = current year.

Sub GetDate()
Dim Response As String
Dim Dt As Date
Dim blnOk As Boolean
Do
Response = InputBox("Enter the contract start date:")
If Response = "" Then Exit Sub
On Error Resume Next
Dt = DateValue(Response)
Select Case Year(Dt)
Case 2006 To 2020
blnOk = True
Case Else
End Select
Loop Until blnOk = True
Range("A1").Value = Dt
End Sub

A calendar, as suggested by Ron, is a good alternative to ensure date
entries.

HTH. Best wishes Harald

"Paige" skrev i melding
...
For the life of me, I cannot get this to work; either it keeps looping,
doesn't return the right message, or bombs out. All I'm trying to do is
ask
the user to input a date in mm/dd/yy format.
1) If they hit cancel or don't enter anything, then they should get a
message saying they must enter a response.
2) If they enter something but it is not in the mm/dd/yy format, they
should
get a message that it is not in the correct format.
3) If they enter it correctly, their response should be put into cell A1.

Can someone PLEASE help me? What I have is:
Sub GetDate()
Dim Response As String
Dim Msg As String
Msg = "Enter the contract start date, in mm/dd/yy format; then click on
'OK'."
Do
Response = InputBox(prompt:=Msg)
If Response = "" Then
MsgBox ("You must enter a response; please try again.")
End If
If Response < Format(CDate(Response), "mm/dd/yy") Then
MsgBox ("Enter a valid date in mm/dd/yy format; please try
again.")
End If
Loop
Range("A1").Value = Response
End Sub

Input Box Asking for Date, and Validating Format
 

Ouch!!! Being that I'm learning, didn't realize this is considered lazy
programming. Thanks guys for your responses/advice; will work through them.
Have a good one!

"Harald Staff" wrote:

You shouldn't require a spesific FORMAT, that's lazy programming, nothing
less. I mean, if a user enters a perfectly valid date in mm/dd/yyyy format
or mmmm d yy format, why on earth should a piece of software reject it and
still expect some respect ? Also, you leave the user no way to cancel the
opertation (which usually returns ""). Please run this code:

Sub Respect()
Dim i As Long
For i = 1 To 20
MsgBox "I won't do it again.", , "Sorry"
Next
End Sub

Now try this, it accepts all common date formats and adjusts to the local
regional settings (mm/dd vs dd/mm). No year (like "Jun 1") = current year.

Sub GetDate()
Dim Response As String
Dim Dt As Date
Dim blnOk As Boolean
Do
Response = InputBox("Enter the contract start date:")
If Response = "" Then Exit Sub
On Error Resume Next
Dt = DateValue(Response)
Select Case Year(Dt)
Case 2006 To 2020
blnOk = True
Case Else
End Select
Loop Until blnOk = True
Range("A1").Value = Dt
End Sub

A calendar, as suggested by Ron, is a good alternative to ensure date
entries.

HTH. Best wishes Harald

"Paige" skrev i melding
...
For the life of me, I cannot get this to work; either it keeps looping,
doesn't return the right message, or bombs out. All I'm trying to do is
ask
the user to input a date in mm/dd/yy format.
1) If they hit cancel or don't enter anything, then they should get a
message saying they must enter a response.
2) If they enter something but it is not in the mm/dd/yy format, they
should
get a message that it is not in the correct format.
3) If they enter it correctly, their response should be put into cell A1.

Can someone PLEASE help me? What I have is:
Sub GetDate()
Dim Response As String
Dim Msg As String
Msg = "Enter the contract start date, in mm/dd/yy format; then click on
'OK'."
Do
Response = InputBox(prompt:=Msg)
If Response = "" Then
MsgBox ("You must enter a response; please try again.")
End If
If Response < Format(CDate(Response), "mm/dd/yy") Then
MsgBox ("Enter a valid date in mm/dd/yy format; please try
again.")
End If
Loop
Range("A1").Value = Response
End Sub