A
Andy
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
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