Trapping invalid dates
 

To All,
I have the following code and have been using the trial and error
method to trap invalid dates with limited success. Most cells on this
particular worksheet are calculated cells that pull information from
different worksheets using the date entered. If I enter an invalid date
all formula's turn to #REF and my code to hide blank cells error's out
and I have to manually force recalculation after fixing the date.

I wont the code to fire only when the date in I1 is changed and
before committing the date to the cell check to make sure that it is
valid and if it is not enter Today's date and display a message that the
date entered is invalid and then go back to I1 so that the date can be
reentered.

I did some searches came up with some of the code below, but I am
cobbling it together and probably do not have the logic correct. Can
someone take a look at the code and point out where I may be going wrong?

Thanks
Harry

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("I1") Then
If Not IsDate(Range("I1")) Then
Application.EnableEvents = False
Range("I1").Value = "1/1/08"
MsgBox ("You entered an invalid date! Please correct"), vbInformation
Application.EnableEvents = True
Exit Sub
Else
'HIDE ROWS BLANK ROWS ON SHEET
Application.ScreenUpdating = False
Application.EnableEvents = True
Application.Calculation = xlCalculationManual
ActiveSheet.Protect Contents:=True, UserInterfaceOnly:=True
Sheets("Income Stmt").Select
With ActiveSheet.UsedRange
.Rows.Hidden = False
For Each Cell In Range("B17:B42")
If Cell.Value = "" Or Cell.Value = 0 Then _
Cell.EntireRow.Hidden = True
Next Cell
End With
With ActiveSheet.UsedRange
For Each Cell In Range("B4:B14")
If Cell.Value = "" Then _
Cell.EntireRow.Hidden = True
Next Cell
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End If
End If
End Sub

Trapping invalid dates
 

from
If Not IsDate(Range("I1")) Then
to
If Not IsDate(Range("I1")) Then
if Range("I1") = datevalue("1/1/2000") then
Application.EnableEvents = False
Range("I1").Value = "1/1/08"
MsgBox ("You entered an invalid date! Please correct"), vbInformation
Application.EnableEvents = True
end if
end if


Also a valid date is any number greater than 1. You may want to limit the
dates to be after 1/1/2000 rather than use ISDATE

from
IsDate(Range("I1"))
to

"HaSt2307" wrote:

To All,
I have the following code and have been using the trial and error
method to trap invalid dates with limited success. Most cells on this
particular worksheet are calculated cells that pull information from
different worksheets using the date entered. If I enter an invalid date
all formula's turn to #REF and my code to hide blank cells error's out
and I have to manually force recalculation after fixing the date.

I wont the code to fire only when the date in I1 is changed and
before committing the date to the cell check to make sure that it is
valid and if it is not enter Today's date and display a message that the
date entered is invalid and then go back to I1 so that the date can be
reentered.

I did some searches came up with some of the code below, but I am
cobbling it together and probably do not have the logic correct. Can
someone take a look at the code and point out where I may be going wrong?

Thanks
Harry

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("I1") Then
If Not IsDate(Range("I1")) Then
Application.EnableEvents = False
Range("I1").Value = "1/1/08"
MsgBox ("You entered an invalid date! Please correct"), vbInformation
Application.EnableEvents = True
Exit Sub
Else
'HIDE ROWS BLANK ROWS ON SHEET
Application.ScreenUpdating = False
Application.EnableEvents = True
Application.Calculation = xlCalculationManual
ActiveSheet.Protect Contents:=True, UserInterfaceOnly:=True
Sheets("Income Stmt").Select
With ActiveSheet.UsedRange
.Rows.Hidden = False
For Each Cell In Range("B17:B42")
If Cell.Value = "" Or Cell.Value = 0 Then _
Cell.EntireRow.Hidden = True
Next Cell
End With
With ActiveSheet.UsedRange
For Each Cell In Range("B4:B14")
If Cell.Value = "" Then _
Cell.EntireRow.Hidden = True
Next Cell
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End If
End If
End Sub

Trapping invalid dates
 

Joel,
Thanks, I did not think about dates prior to 2000. But when I fat
finger a date enter 9/32 versus 9/3 or 9/2, this did not trap this
invalid date although the underlying date show 9/1/1932 in the formula
bar and September 32 in the cell. So I am also trying catch fat finger
dates.

Where else should I be looking? Also should this whole code be
before the exit sub or should the last end if be just before the whole
subroutine end if?

Thanks
Harry

Joel wrote:
from
If Not IsDate(Range("I1")) Then
to
If Not IsDate(Range("I1")) Then
if Range("I1") = datevalue("1/1/2000") then
Application.EnableEvents = False
Range("I1").Value = "1/1/08"
MsgBox ("You entered an invalid date! Please correct"), vbInformation
Application.EnableEvents = True
end if
end if


Also a valid date is any number greater than 1. You may want to limit the
dates to be after 1/1/2000 rather than use ISDATE

from
IsDate(Range("I1"))
to

"HaSt2307" wrote:

To All,
I have the following code and have been using the trial and error
method to trap invalid dates with limited success. Most cells on this
particular worksheet are calculated cells that pull information from
different worksheets using the date entered. If I enter an invalid date
all formula's turn to #REF and my code to hide blank cells error's out
and I have to manually force recalculation after fixing the date.

I wont the code to fire only when the date in I1 is changed and
before committing the date to the cell check to make sure that it is
valid and if it is not enter Today's date and display a message that the
date entered is invalid and then go back to I1 so that the date can be
reentered.

I did some searches came up with some of the code below, but I am
cobbling it together and probably do not have the logic correct. Can
someone take a look at the code and point out where I may be going wrong?

Thanks
Harry

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("I1") Then
If Not IsDate(Range("I1")) Then
Application.EnableEvents = False
Range("I1").Value = "1/1/08"
MsgBox ("You entered an invalid date! Please correct"), vbInformation
Application.EnableEvents = True
Exit Sub
Else
'HIDE ROWS BLANK ROWS ON SHEET
Application.ScreenUpdating = False
Application.EnableEvents = True
Application.Calculation = xlCalculationManual
ActiveSheet.Protect Contents:=True, UserInterfaceOnly:=True
Sheets("Income Stmt").Select
With ActiveSheet.UsedRange
.Rows.Hidden = False
For Each Cell In Range("B17:B42")
If Cell.Value = "" Or Cell.Value = 0 Then _
Cell.EntireRow.Hidden = True
Next Cell
End With
With ActiveSheet.UsedRange
For Each Cell In Range("B4:B14")
If Cell.Value = "" Then _
Cell.EntireRow.Hidden = True
Next Cell
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End If
End If
End Sub

Trapping invalid dates
 

I got the sign backwards

from
if Range("I1") = datevalue("1/1/2000") then

to
if Range("I1") <= datevalue("1/1/2000") then


"HaSt2307" wrote:

Joel,
Thanks, I did not think about dates prior to 2000. But when I fat
finger a date enter 9/32 versus 9/3 or 9/2, this did not trap this
invalid date although the underlying date show 9/1/1932 in the formula
bar and September 32 in the cell. So I am also trying catch fat finger
dates.

Where else should I be looking? Also should this whole code be
before the exit sub or should the last end if be just before the whole
subroutine end if?

Thanks
Harry

Joel wrote:
from
If Not IsDate(Range("I1")) Then
to
If Not IsDate(Range("I1")) Then
if Range("I1") = datevalue("1/1/2000") then
Application.EnableEvents = False
Range("I1").Value = "1/1/08"
MsgBox ("You entered an invalid date! Please correct"), vbInformation
Application.EnableEvents = True
end if
end if


Also a valid date is any number greater than 1. You may want to limit the
dates to be after 1/1/2000 rather than use ISDATE

from
IsDate(Range("I1"))
to

"HaSt2307" wrote:

To All,
I have the following code and have been using the trial and error
method to trap invalid dates with limited success. Most cells on this
particular worksheet are calculated cells that pull information from
different worksheets using the date entered. If I enter an invalid date
all formula's turn to #REF and my code to hide blank cells error's out
and I have to manually force recalculation after fixing the date.

I wont the code to fire only when the date in I1 is changed and
before committing the date to the cell check to make sure that it is
valid and if it is not enter Today's date and display a message that the
date entered is invalid and then go back to I1 so that the date can be
reentered.

I did some searches came up with some of the code below, but I am
cobbling it together and probably do not have the logic correct. Can
someone take a look at the code and point out where I may be going wrong?

Thanks
Harry

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("I1") Then
If Not IsDate(Range("I1")) Then
Application.EnableEvents = False
Range("I1").Value = "1/1/08"
MsgBox ("You entered an invalid date! Please correct"), vbInformation
Application.EnableEvents = True
Exit Sub
Else
'HIDE ROWS BLANK ROWS ON SHEET
Application.ScreenUpdating = False
Application.EnableEvents = True
Application.Calculation = xlCalculationManual
ActiveSheet.Protect Contents:=True, UserInterfaceOnly:=True
Sheets("Income Stmt").Select
With ActiveSheet.UsedRange
.Rows.Hidden = False
For Each Cell In Range("B17:B42")
If Cell.Value = "" Or Cell.Value = 0 Then _
Cell.EntireRow.Hidden = True
Next Cell
End With
With ActiveSheet.UsedRange
For Each Cell In Range("B4:B14")
If Cell.Value = "" Then _
Cell.EntireRow.Hidden = True
Next Cell
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End If
End If
End Sub

Trapping invalid dates
 

Joel,
Thanks for the help. Here is what I finally got to work the way I
wanted...a lot of trial and error. The is not date was not working the
way I thought it should.

If Range("I1").Value < DateValue("1/1/2006") Then
Application.EnableEvents = False
Range("I1").Value = Date
MsgBox ("You entered an invalid date! Please correct"),
vbInformation
Application.EnableEvents = True

Thanks again.
Harry

Joel wrote:
I got the sign backwards

from
if Range("I1") = datevalue("1/1/2000") then

to
if Range("I1") <= datevalue("1/1/2000") then


"HaSt2307" wrote:

Joel,
Thanks, I did not think about dates prior to 2000. But when I fat
finger a date enter 9/32 versus 9/3 or 9/2, this did not trap this
invalid date although the underlying date show 9/1/1932 in the formula
bar and September 32 in the cell. So I am also trying catch fat finger
dates.

Where else should I be looking? Also should this whole code be
before the exit sub or should the last end if be just before the whole
subroutine end if?

Thanks
Harry

Joel wrote:
from
If Not IsDate(Range("I1")) Then
to
If Not IsDate(Range("I1")) Then
if Range("I1") = datevalue("1/1/2000") then
Application.EnableEvents = False
Range("I1").Value = "1/1/08"
MsgBox ("You entered an invalid date! Please correct"), vbInformation
Application.EnableEvents = True
end if
end if


Also a valid date is any number greater than 1. You may want to limit the
dates to be after 1/1/2000 rather than use ISDATE

from
IsDate(Range("I1"))
to

"HaSt2307" wrote:

To All,
I have the following code and have been using the trial and error
method to trap invalid dates with limited success. Most cells on this
particular worksheet are calculated cells that pull information from
different worksheets using the date entered. If I enter an invalid date
all formula's turn to #REF and my code to hide blank cells error's out
and I have to manually force recalculation after fixing the date.

I wont the code to fire only when the date in I1 is changed and
before committing the date to the cell check to make sure that it is
valid and if it is not enter Today's date and display a message that the
date entered is invalid and then go back to I1 so that the date can be
reentered.

I did some searches came up with some of the code below, but I am
cobbling it together and probably do not have the logic correct. Can
someone take a look at the code and point out where I may be going wrong?

Thanks
Harry

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("I1") Then
If Not IsDate(Range("I1")) Then
Application.EnableEvents = False
Range("I1").Value = "1/1/08"
MsgBox ("You entered an invalid date! Please correct"), vbInformation
Application.EnableEvents = True
Exit Sub
Else
'HIDE ROWS BLANK ROWS ON SHEET
Application.ScreenUpdating = False
Application.EnableEvents = True
Application.Calculation = xlCalculationManual
ActiveSheet.Protect Contents:=True, UserInterfaceOnly:=True
Sheets("Income Stmt").Select
With ActiveSheet.UsedRange
.Rows.Hidden = False
For Each Cell In Range("B17:B42")
If Cell.Value = "" Or Cell.Value = 0 Then _
Cell.EntireRow.Hidden = True
Next Cell
End With
With ActiveSheet.UsedRange
For Each Cell In Range("B4:B14")
If Cell.Value = "" Then _
Cell.EntireRow.Hidden = True
Next Cell
End With
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End If
End If
End Sub