Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Validation between two Times
Good Afternoon.
I have some code (below) that adds validation to a certain cell to allow the user only to fill in a value between a certain time, such as 11:00:00 AM and 11:59:00 AM in cell C22. There is similar code for cells C11:C34. The below code works fine when inputting the correct or incorrect values but it seems to have a life of its own when copy and pasting data into the cells. For example when I paste in the following data to cells C11:C34 the validation kicks in and says it is incorrect even though it is not. If I input the same value into the cell directly there is no error and the cell that sparks the message is not always the same one. The cells are formatted hh:mm. 00:00 01:00 02:00 03:00 04:00 05:00 06:00 07:00 08:00 09:00 10:00 11:00 12:00 13:00 14:00 15:00 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00 ' Set C22Valrange = Range("C22") For Each cell In Target If Union(cell, C22Valrange).Address = C22Valrange.Address Then If cell.Value = "" Then DataOk = True Else If cell.Value = #11:00:00 AM# And cell.Value <= #11:59:00 AM# Then DataOk = True Else DataOk = False cell.Clear cell.Locked = False ActiveCell.Offset(-1, 0).Select End If End If End If Next cell If Not DataOk Then msg = "Please enter a time between 11:00 (11AM) and 11:59" MsgBox msg, vbCritical GoTo LeaveValidation End If ' Any help would be greatly appreciated Andy |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Calculation of hourly rate times hours times 1.5 | Excel Worksheet Functions | |||
How are relay leg times or driving times entered and totaled? | Excel Worksheet Functions | |||
Charting and analyzing Times' times data for trends | Excel Discussion (Misc queries) | |||
Data Validation - Drop-down list - make arrow visible at all times | Excel Discussion (Misc queries) | |||
validation of times | Excel Worksheet Functions |