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

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

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

It does allow text input, but it gets caught in the error trap.

InputBox doesn't have a date type, but it does have a number type, which is
a bit better.

As to the loop, the problem with that is you can get stuck in the d#!m
things. What I tend to do is to just cancel the event in those cases, so
they can try and close again, but at least they don't get stuck.

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(Prompt:=" No Date Entered" & _
Chr(13) & Chr(10) & Chr(13) & Chr(10) & Chr(13) & Chr(10) &
_
"Enter Date now!", _
Title:="Date not entered", _
Default:=Format(Date, "dd/mm/yyyy"), _
Type:=1)
If IsDate(tmp) And _
tmp = Date And _
tmp <= Date + 4 Then
mydate.Value = Format(tmp, "DD/MM/YYYY")
Else
MsgBox "Error, not closing"
cancel = True
End If
End If

End Sub




--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"stumac" wrote in message
...
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

Hi Bob, thanks for your help with this. I got stuck in a few loops myself
while trying this but eventually got it to work by setting the tmp variable
to a date, now you have told me I can set the input box to a number it seems
to be running perfectly.

Stu

"Bob Phillips" wrote:

It does allow text input, but it gets caught in the error trap.

InputBox doesn't have a date type, but it does have a number type, which is
a bit better.

As to the loop, the problem with that is you can get stuck in the d#!m
things. What I tend to do is to just cancel the event in those cases, so
they can try and close again, but at least they don't get stuck.

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(Prompt:=" No Date Entered" & _
Chr(13) & Chr(10) & Chr(13) & Chr(10) & Chr(13) & Chr(10) &
_
"Enter Date now!", _
Title:="Date not entered", _
Default:=Format(Date, "dd/mm/yyyy"), _
Type:=1)
If IsDate(tmp) And _
tmp = Date And _
tmp <= Date + 4 Then
mydate.Value = Format(tmp, "DD/MM/YYYY")
Else
MsgBox "Error, not closing"
cancel = True
End If
End If

End Sub




--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"stumac" wrote in message
...
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

Application.inputbox has a parm that you can use that forces the user to enter
numbers--or hit cancel. They can't even leave the application.inputbox if they
type text (type:=1 is the parm).

Maybe...

Option Explicit
Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim myDate As Range
Dim tmp As Date

Set myDate = Range("I2")
If myDate.Value = "Enter Date" Then
tmp = Application.InputBox(Prompt:="No Date Entered" & _
String(3, vbNewLine) & _
"Enter Date now!", Title:="Date not entered", _
Default:=Date, Type:=1)
If IsDate(tmp) And _
tmp = Date And _
tmp <= Date + 4 Then
With myDate
.Value = tmp
.NumberFormat = "dd/mm/yyyy"
End With
Else
MsgBox "Error"
'Cancel = True 'stop the save???
End If
End If

End Sub




stumac wrote:

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




--

Dave Peterson