Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel,microsoft.public.excel.programming
|
|||
|
|||
Validation code not working with pasted data
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2007 data validation not working | Excel Discussion (Misc queries) | |||
Macro code to center pasted data in a column | Excel Discussion (Misc queries) | |||
Data Validation not working!! Please help!!!! | Excel Discussion (Misc queries) | |||
Crazy Data Validation ... List Validation Not Working | Excel Programming | |||
Data Validation Not Working | Excel Discussion (Misc queries) |