Many thanks. It does not now accept single digit or two digit date only
entry. However:

1. It somehow still accepts date and month only entry, and
2. Manages to switch date and month if date <=12.



"Paige" wrote:

Try this; even if they put mm/dd/yy (or other) format in the box, it will
correct the format when entered into the cell.

Sub AskForDate2()
Dim MSG As String
Dim USERENTRY As String
Dim DATEVALUE As Date
Dim MINDATEVAL As Date
Dim MAXDATEVAL As Date

MINDATEVAL = DateSerial(1947, 8, 15)
MAXDATEVAL = Format(Now, "dd/mm/yyyy")

If ActiveCell.Value = "" Then
DATEVALUE = IsDate(USERENTRY)
MSG = "There is no date in the 'Date' field. If you want to use the
current " & _
"date, then just click on 'OK'. If not, then enter another date below
(in " & _
"dd/mm/yyyy format), then click on 'OK'."
Do
USERENTRY = InputBox(MSG, "PLEASE ENTER THE 'DATE'", Format(Now,
"dd/mm/yyyy"))
If USERENTRY = "" Then
MsgBox ("You must enter a response; please try again.")
End If
If IsDate(USERENTRY) = True Then
If USERENTRY = MINDATEVAL And USERENTRY <= MAXDATEVAL Then
Exit Do
End If
If USERENTRY < MINDATEVAL Or USERENTRY MAXDATEVAL Then
MSG = "Your previous entry was invalid. Please enter a value
between " & Format(MINDATEVAL, "dd/mm/yyyy") & " and " & Format(MAXDATEVAL,
"dd/mm/yyyy") & "."
End If
End If
Loop
ActiveCell.Value = USERENTRY
ActiveCell.NumberFormat = "dd/mm/yyyy;@"
End If
End Sub

"a m spock" wrote:

My friend, I am happy to report some progress:

Working with your code I have written the following. Which works for
entering and formatting the date. But it still accepts incomplete input and
completes it automatically to the nearest guess. Can this be prevented so
that only completed data is accepted?

Sub AskForDate()
Dim MSG As String
Dim USERENTRY As String
Dim DATEVALUE As Boolean

If ActiveCell.Value = "" Then
DATEVALUE = IsDate(USERENTRY)
MSG = "There is no date in the 'Date' field. If you want to use the current
date, then just click on 'OK'. If not, enter another date below (in
dd/mm/yyyy format), then click on"
'OK'."

Do
USERENTRY = InputBox(MSG, "PLEASE ENTER THE 'DATE'", Format(Now,
"dd/mm/yyyy"))

If USERENTRY = "" Then
MsgBox ("You must enter a response; please try again.")
End If

If IsDate(USERENTRY) = True Then

End If
Exit Do
Loop
ActiveCell.Value = USERENTRY
ActiveCell.NumberFormat = "dd/mm/yyyy;@"
End If
End Sub


NOTE: I had to add one more End If before End Sub to make it work error free.


"Paige" wrote:

Not sure how much "validation" you want to do on what is entered, but this
asks for a date (and puts in the current date as the default date in the
input box), then inserts the user response into E15 and formats. If you
don't want a default date in the input box, then where it says "USERENTRY =
InputBox...", remove the Format(Now, "mm/dd/yyyy") part of that line. Also,
you can change how you want the date formatted by changing, for example,
mm/dd/yyyy to mm/dd/yy. If you want more "validation" that this, then one of
the more experienced folks will have to help. Hope this is useful.

Sub AskForDate()
Dim MSG As String
Dim USERENTRY As String
Dim DATEVALUE As Boolean

If Worksheets("Input and P&L").Range("E15").Value = "" Then
DATEVALUE = IsDate(USERENTRY)
MSG = "There is no date in the 'Date Prepared' field on the Input and
P&L tab. If you want to use the current date, then just click on 'OK'. If
not, enter another date below (in mm/dd/yyyy or m/d/yy format), then click on
'OK'."

Do
USERENTRY = InputBox(MSG, "PLEASE ENTER THE 'DATE PREPARED'",
Format(Now, "mm/dd/yyyy"))
If USERENTRY = "" Then
MsgBox ("You must enter a response; please try again.")
End If
If IsDate(USERENTRY) = True Then
Exit Do
End If
Loop
Worksheets("Input and P&L").Range("E15").Value = USERENTRY
Worksheets("Input and P&L").Range("E15").NumberFormat = "mm/dd/yyyy;@"
End Sub

"a m spock" wrote:

I have a macro working which populates a table with user data entry with
input prompts. The data needs to be validated. Particularly one cell with
date input. When entering directly into cell the validation rules work and an
error message is generated for invalid data. But when entered through "Input
prompts" the invalid data is accepted. Any way around this?? Please help.