Data validation not working

[waheed254]

Dear All,
I am facing problem regarding Data Validation that when i Type data in data validation activated cell it shows error when i type Data which is not available in my list but when i copy Paste/Paste Special in Same cell it do not show error it just Paste value if it is in my list or Not. I think an error must be displayed while copy pasting data if it is not in my specified List if Source Data...


Can anyone Help...

 

[waheed254]

Sample sheet is attached for more Clarity. in first sheet data is entered while data validation is active but in D6 and E6 i copied & Pasted values and this act bypassed Data validation....

 

[Peter_SSs]

Sample sheet is attached ..

As you've probably now realised, attachments are not allowed here. However, you can post small screen shots directly in your post - see my signature block for suggestion of how to do that. Alternatively, answering the questions from my previous post may possibly shed enough light.

I understand the issue of Data Validation being over-ridden by pasting, I am trying to understand where your Data Validation is and what the Actual Data Validation is (that is: is it preventing duplicates? does it just require an entry from a pre-defined list?, does it require a whole number between 5 and 20? is it a custom data validation formula? etc)

 

[waheed254]

it just require an entry from a pre-defined list...

 

[waheed254]

Any Possible Solution ???

 

[Peter_SSs]

Any Possible Solution ???

Probably, but if you don't answer the questions asked to clarify, it's hard to proceed.

.. answering the questions from my previous post may possibly shed enough light.

I understand the issue of Data Validation being over-ridden by pasting, I am trying to understand where your Data Validation is and what the Actual Data Validation is (that is: is it preventing duplicates? does it just require an entry from a pre-defined list?, does it require a whole number between 5 and 20? is it a custom data validation formula? etc)

Now you have answered (partially) what the Data Validation is, though you still haven't answered where the pre-defined list is located.

You have not answered where the data Validation cells themselves are.

I tried to confirm both of these issues with my questions in post #10.

 

[waheed254]

Data Validation Cells are in Sheet Named "Collection" from C4:C5000 and Predefined list is in Sheet 2 Named "DLR" from D4:D3000. AND SAME again in "Collection" from D4:D5000 and Predefined list is in Sheet 2 Named "DLR" from E4:E3000..


Hope it will be usefull..

 

[Peter_SSs]

Data Validation Cells are in Sheet Named "Collection" from C4:C5000 and Predefined list is in Sheet 2 Named "DLR" from D4:D3000. AND SAME again in "Collection" from D4:D5000 and Predefined list is in Sheet 2 Named "DLR" from E4:E3000..


Hope it will be usefull..

That is much more useful.
However, it is getting late here now & I'm about to sign off for the night, and I may not be able get back to this for a day or two. I'll look again when I can.

 

[waheed254]

Thnx for Help but i am waiting for solution...

 

[Peter_SSs]

Try this in place of your Data Validation. To implement ..

1. Right click the 'Collection' sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window & test.

4. If using Excel 2007 or later your workbook will need to be saved as a macro-enabled file (*.xlsm)

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim ChangedC As Range, ChangedD As Range, c As Range, Found As Range
  Dim DVListC As Range, DVLIstD As Range
  Dim sInvalid As String
  Dim val As Variant
  
  Set DVListC = Sheets("DLR").Range("D4:D3000")
  Set DVLIstD = Sheets("DLR").Range("E4:E3000")
  
  Set ChangedC = Intersect(Target, Range("C4:C5000"))
  Set ChangedD = Intersect(Target, Range("D4:D5000"))
  
  Application.EnableEvents = False
  If Not ChangedC Is Nothing Then
    For Each c In ChangedC
      val = c.Value
      If Len(val) Then
        Set Found = DVListC.Find(What:=val, LookIn:=xlValues, _
            LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Found Is Nothing Then
          sInvalid = sInvalid & vbLf & c.Address(0, 0) & vbTab & val
          c.ClearContents
        End If
      End If
    Next c
  End If
  
  If Not ChangedD Is Nothing Then
    For Each c In ChangedD
      val = c.Value
      If Len(val) Then
        Set Found = DVLIstD.Find(What:=val, LookIn:=xlValues, _
            LookAt:=xlWhole, MatchCase:=False, SearchFormat:=False)
        If Found Is Nothing Then
          sInvalid = sInvalid & vbLf & c.Address(0, 0) & vbTab & val
          c.ClearContents
        End If
      End If
    Next c
  End If
  Application.EnableEvents = True
  
  If Len(sInvalid) Then
    MsgBox "The following entries were invalid and have been removed" & sInvalid
  End If
  
End Sub

 

[waheed254]

Great, it worked Perfect, thank you very much.... can you plz modify this code to Highlight in Red Color those cells which are not available in given list rather than removing...