Date Entry through input prompt

[a m spock]

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.

 

[Paige]

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]

Many thanks. We are almost there. But being a novice at this, I need your
help to take the next and final step.

The code I presently have in the macro is:

ActiveCell.Select
ActiveCell.Value = InputBox("Enter Date as DD-MM-YYYY.")

The validation rule: is a date between 15-08-1947 and "Today". I have
alreday set this validation rule in the ActiveCell, but seems to be useless.

Default date is not needed. But ideally, though not necessarily, a greyed
out format indicator, if feasible, would be great.

The error message needed in case of blank or invalid date : ""Please enter a
date between 15-08-1947 and Today"

Could you help with a sub which I can call from within another macro to
accomplish this?

I hope I have expressed clearly!

 

[a m spock]

I also need to make sure that any data other than the DD-MM-YYYY is not
accepted. For example in the macro you recommended the entry 5 is accepted as
a valid entry
05-01-1900

 

[a m spock]

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]

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]

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]

Well, this is beyond my capability, since I'm self-taught. If you ask them
to put it in as dd/mm/yyyy but they put it in as mm/dd/yyyy instead, then
there is no way to know in many cases. For example, they can enter
05/01/2008, but we don't know if they actually meant May 1, 2008 or January
5, 2008. Does this make sense?

 

[a m spock]

Thanks for trying anyway. What it does is if I enter 05/06/2008, it returns
06/05/2008 and vice versa!!

This is crazy but is obviously an issue between VBA and Excel. There might
be a bypass which as you say needs more expertise.

Once again, thanks.

 

[Paige]

Maybe it would be easier to ask them to enter the date as it is normally done
- mm/dd/yyyy, then have Excel format the entry as dd/mm/yyyy. But again,
this assumes they DO enter mm/dd/yyyy; if they accidentally enter dd/mm/yyyy,
Excel will think they entered mm/dd/yyyy and the result will be reversed;
i.e., they enter 05/03/2008 meaning March 5, Excel will reverse it to
03/05/2008.