Hi Bob, i have tried this but it still seems to allow text values. Also is
there a way to loop the statement until a valid value is entered. I have
tried with the Excel help but cant get it to work.

Thanks



"Bob Phillips" wrote:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim mydate As Range
Dim tmp
Set mydate = Range("I2")
If mydate = "Enter Date" Then
tmp = Application.InputBox(" No Date Entered" & _
Chr(13) & Chr(10) & Chr(13) & Chr(10) & Chr(13) &
Chr(10) & _
"Enter Date now!", "Date not entered", Format(Date,
"dd/mm/yyyy"))
If IsDate(tmp) And _
tmp = Date And _
tmp <= Date + 4 Then
mydate.Value = Format(tmp, "DD/MM/YYYY")
Else
MsgBox "Error"
End If
End If

End Sub

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"stumac" wrote in message
...
Hi, I have just started some very basic VB my code needs to check if cell
I2
= "Enter Date" if it does it brings up an input box for the user to enter
the
date. However just now the user can enter anything into this input box
and
Excel will accept it. I would like it to reject anything that is not a
within the next or last 4 days and continue asking for a date before
closing.
Can someone tell me how I would do this? My code is below. Thanks in
advance


Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim mydate As Range
Set mydate = Worksheets("Daily Sheet").Range("I2")
If mydate = "Enter Date" Then mydate = Application.InputBox(" No Date
Entered" & Chr(13) & Chr(10) & Chr(13) & Chr(10) & Chr(13) & Chr(10) & "
Enter Date now!", "Date not entered", Format(Date, "dd/mm/yyyy"))
mydate = Format(mydate, "DD/MM/YYYY")
End Sub