Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
question data validation and delete/clear behavior
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.EnableEvent s = False Target.Select Target.Value = "" Workbooks("Timesheet.xls").Application.EnableEvent s = True Exit Sub End If If Target.Value < 0 Or Target.Value 2400 Then Workbooks("Timesheet.xls").Application.EnableEvent s = False Target.Select Target.Value = "" Workbooks("Timesheet.xls").Application.EnableEvent s = 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.EnableEvent s = False Target.Select Target.Value = "" Workbooks("Timesheet.xls").Application.EnableEvent s = 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.EnableEvent s = False Target.Select Target.Value = "" Workbooks("Timesheet.xls").Application.EnableEvent s = 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.EnableEvent s = False Target.Value = Format(Target.Value, "00:00") Workbooks("Timesheet.xls").Application.EnableEvent s = True Exit Sub End If |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Data Validation - Prevent delete | Excel Discussion (Misc queries) | |||
Home key behavior question | New Users to Excel | |||
DataValidation Question | New Users to Excel | |||
Question about hyperlink behavior... | Excel Programming |