Validation code not working with pasted data

L

Les Isaacs

Hello All

I have a worksheet that has one column with data validation in place and
some other columns (I and V to AW) with some validation vba code in place.
Everything works fine when data is entered by typing it into the cells, but
neither the dv nor the vba is effective if data is pasted into the cells. Is
there any way I can get round this?

The vba validation code is below (if this helps).

Hope someone can help
Many thanks
Les

the code:
Option Explicit

Dim prev
Private Const WS_RANGE_PC As String = "I1:I999"
Private Const WS_RANGE_TIME As String = "V2:AW99"

Private Sub Worksheet_Change(ByVal Target As Range)
Dim TimeStr As String

Application.EnableEvents = False

On Error GoTo ws_exit


If Not Intersect(Target, Me.Range(WS_RANGE_PC)) Is Nothing Then
With Target
If Not ValidatePostCode(.Value) Then
If prev = "" Then
MsgBox "Invalid postcode."
Else
MsgBox "Invalid postcode, reverting to " & prev
End If

.Value = prev
.Select
End If

.Value = UCase(.Value)
End With
Else

On Error GoTo EndMacro

If Target.Cells.Count > 1 Then

Application.EnableEvents = True
Exit Sub
End If
If Target.Value = "" Then

Application.EnableEvents = True
Exit Sub
End If
If Application.Intersect(Target, Me.Range(WS_RANGE_TIME)) Is Nothing
Then
Application.EnableEvents = True
Exit Sub
End If

Application.EnableEvents = False
With Target
If .HasFormula = False Then
Select Case Len(.Value)
Case 1 ' e.g., 1 = 00:01 AM
TimeStr = "00:0" & .Value
Case 2 ' e.g., 12 = 00:12 AM
TimeStr = "00:" & .Value
Case 3 ' e.g., 735 = 7:35 AM
TimeStr = Left(.Value, 1) & ":" & Right(.Value, 2)
Case 4 ' e.g., 1234 = 12:34
TimeStr = Left(.Value, 2) & ":" & Right(.Value, 2)
Case 5 ' e.g., 12345 = 1:23:45 NOT 12:03:45
TimeStr = Left(.Value, 1) & ":" & Mid(.Value, 2, 2)
& ":" & Right(.Value, 2)
Case 6 ' e.g., 123456 = 12:34:56
TimeStr = Left(.Value, 2) & ":" & Mid(.Value, 3, 2)
& ":" & Right(.Value, 2)
Case Else
Err.Raise 0
End Select
.Value = TimeValue(TimeStr)
End If

If Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(1).Column).Value
<> "" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(2).Column).Value <>
"" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(1).Column).Value >
Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(2).Column).Value Then
MsgBox "Start time earlier than end time"
.Value = ""
End If
If Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(3).Column).Value
<> "" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(4).Column).Value <>
"" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(3).Column).Value >
Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(4).Column).Value Then
MsgBox "Start time earlier than end time"
.Value = ""
End If

If Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(5).Column).Value
<> "" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(6).Column).Value <>
"" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(5).Column).Value >
Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(6).Column).Value Then
MsgBox "Start time earlier than end time"
.Value = ""
End If

If Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(7).Column).Value
<> "" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(8).Column).Value <>
"" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(7).Column).Value >
Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(8).Column).Value Then
MsgBox "Start time earlier than end time"
.Value = ""
End If

If Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(9).Column).Value
<> "" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(10).Column).Value
<> "" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(9).Column).Value >
Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(10).Column).Value Then
MsgBox "Start time earlier than end time"
.Value = ""
End If

If Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(11).Column).Value
<> "" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(12).Column).Value
<> "" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(11).Column).Value >
Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(12).Column).Value Then
MsgBox "Start time earlier than end time"
.Value = ""
End If

If Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(13).Column).Value
<> "" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(14).Column).Value
<> "" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(13).Column).Value >
Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(14).Column).Value Then
MsgBox "Start time earlier than end time"
.Value = ""
End If

If Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(15).Column).Value
<> "" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(16).Column).Value
<> "" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(15).Column).Value >
Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(16).Column).Value Then
MsgBox "Start time earlier than end time"
.Value = ""
End If

If Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(17).Column).Value
<> "" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(18).Column).Value
<> "" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(17).Column).Value >
Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(18).Column).Value Then
MsgBox "Start time earlier than end time"
.Value = ""
End If

If Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(19).Column).Value
<> "" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(20).Column).Value
<> "" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(19).Column).Value >
Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(20).Column).Value Then
MsgBox "Start time earlier than end time"
.Value = ""
End If

If Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(21).Column).Value
<> "" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(22).Column).Value
<> "" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(21).Column).Value >
Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(22).Column).Value Then
MsgBox "Start time earlier than end time"
.Value = ""
End If

If Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(23).Column).Value
<> "" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(24).Column).Value
<> "" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(23).Column).Value >
Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(24).Column).Value Then
MsgBox "Start time earlier than end time"
.Value = ""
End If

If Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(25).Column).Value
<> "" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(26).Column).Value
<> "" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(25).Column).Value >
Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(26).Column).Value Then
MsgBox "Start time earlier than end time"
.Value = ""
End If

If Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(27).Column).Value
<> "" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(28).Column).Value
<> "" And Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(27).Column).Value >
Me.Cells(.Row, Me.Range(WS_RANGE_TIME).Columns(28).Column).Value Then
MsgBox "Start time earlier than end time"
.Value = ""
End If

End With
End If

Application.EnableEvents = True

Exit Sub
EndMacro:

Application.EnableEvents = True

MsgBox "You did not enter a valid time. Do not use colons etc. - " & _
"enter 8.00am as 800, enter 4.30pm as 1630 etc."
Target.Value = ""

ws_exit:

Application.EnableEvents = True
End Sub


Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Application.EnableEvents = True
If Not Intersect(Target, Me.Range(WS_RANGE_PC)) Is Nothing Then
prev = Target.Value
End If
End Sub
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with this error "PasteSpecial method of Range class failed !! 0
Help with this code 9
COMBINE TWO MACROS INTO ONE 10
Worksheet Change event - not running when down arrow is pressed 3
Code stopped working 2
Help with sorting issue?? 10
Code to replace 'sumproduct' 18
CORRECTION NEEDED 2

Top