question data validation and delete/clear behavior

[Jeff Higgins]

Excel 2003
Hi, I'm having problems with delete/clear behavior in a time sheet
application that I'm working on and I hope someone will help.

I have a range named TimeEntry that contains 14 columns with 6 rows each:
Row[1] a time-In
Row[2] a time-Out
Row[3] a time-In
Row[4] a time-Out
Row[5] a time-In
Row[6] a time-Out
followed by a total row which contains the formula
=((Row2-Row1)+(Row3-Row4)+(Row5-Row6))*24

I validate user entries in the WorksheetChange handler using the code that
follows.
The sheet is protected.

The problem is that when a user clears an entry that unbalances an In/Out
pair of cells the total becomes invalid. I do want the user to be able to
clear the entire TimeEntry range, an entire column, and individual In/Out
pairs in the column and also to be able to paste valid entries.

Bonus behavior would allow the user to enter In and Out times in arbitrary
places in the column for instance:
a time In in Row[1] and a time Out in Row[6]
or,
a time In in Row[3] and a time Out in Row[4] and a time In in Row[5] and a
time Out in Row[6]
etc.

If I haven't made my request for help clear or haven't provided enough info
please ask.
Thanks in advance for any help.

Jeff Higgins

Code follows:

'Catch Range array events. i.e. Cut, Copy, Delete, Clear etc.
If VarType(Target.Value) > vbUserDefinedType Then
Exit Sub
End If

'Does not attempt to validate changes outside the range named "Validate".
If Intersect(Target, Range("Validate")) Is Nothing Then
Exit Sub
End If

If Not Intersect(Target, Range("TimeEntry")) Is Nothing Then
If Not VarType(Target.Value) = vbDouble Then
Workbooks("Timesheet.xls").Application.EnableEvents = False
Target.Select
Target.Value = ""
Workbooks("Timesheet.xls").Application.EnableEvents = True
Exit Sub
End If

If Target.Value < 0 Or Target.Value > 2400 Then
Workbooks("Timesheet.xls").Application.EnableEvents = False
Target.Select
Target.Value = ""
Workbooks("Timesheet.xls").Application.EnableEvents = True
MsgBox "Please enter time as a number between 0 and 2400" &
Chr(13) & Chr(13) & _
"For example:" & Chr(13) & "enter midnight as 0. Displays as 0:00" &
Chr(13) & _
"enter noon as 1200. Displays as 12:00" & Chr(13) & "enter 5 PM as 1700.
Displays as 17:00"
Exit Sub
End If

If Target.Value Mod 100 > 59 Then
Workbooks("Timesheet.xls").Application.EnableEvents = False
Target.Select
Target.Value = ""
Workbooks("Timesheet.xls").Application.EnableEvents = True
MsgBox "Please enter time in 24hour/60minute format."
Exit Sub
End If

If Intersect(Target, Range("TimeEntryFirst")) Is Nothing Then
If Target.Offset(-1, 0) = Empty Or Target.Offset(-1, 0).Value *
24 * 100 > Target.Value Then
Workbooks("Timesheet.xls").Application.EnableEvents = False
Target.Select
Target.Value = ""
Workbooks("Timesheet.xls").Application.EnableEvents = True
MsgBox "Please enter an ""OUT"" time later than the ""IN""
time above."
Exit Sub
End If
End If
'Change "24hour/60minute" entry format to HH:MM display format.
Workbooks("Timesheet.xls").Application.EnableEvents = False
Target.Value = Format(Target.Value, "00:00")
Workbooks("Timesheet.xls").Application.EnableEvents = True
Exit Sub
End If