Help Needed for Drop Down Validation

[NonStopLeo]

Friends, I need a little help, if you can please do.
In below link, I have attached my sheet. In Column E, i have given a drop down list from where any value can be pasted. but whenever I copy some value from other sheet and paste, it is also accepting values other than listed in drop down list. I need some validation, that the value should be selected from drop down list only, if wrong value is pasted which is not in list, should not be accepted, only listed value should be accepted. thanks.

https://jumpshare.com/v/aXxA2jvq9ctYgvRmf7sb

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macros into the empty code window that opens up. Change the range in the code to suit your needs. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If HasValidation(Range("E2:E5")) Then
        Exit Sub
    Else
        Application.Undo
        MsgBox "Error: You cannot paste data into these cells." & _
        "Please select from the drop-down list.", vbCritical
        Application.CutCopyMode = False
    End If
End Sub

Private Function HasValidation(r) As Boolean
    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

 

[Fluff]

mumps
I maybe wrong, but that looks like a permanent loop.

 

[Fluff]

On top of what I said in post#3.
It wont let you select from the dropdown

 

[mumps]

@ Fluff: Thanks for you input. When I tried it on the file the OP attached in his link, it worked properly.

 

[Fluff]

It would have helped if my DV was in col E not col H.
Apologies

 

[NonStopLeo]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab for your sheet and click 'View Code'. Paste the macros into the empty code window that opens up. Change the range in the code to suit your needs. Close the code window to return to your sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If HasValidation(Range("E2:E5")) Then
        Exit Sub
    Else
        Application.Undo
        MsgBox "Error: You cannot paste data into these cells." & _
        "Please select from the drop-down list.", vbCritical
        Application.CutCopyMode = False
    End If
End Sub

Private Function HasValidation(r) As Boolean
    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

hi mumps, thank for the code but it is not giving the desired results in my sheet.
what i want to do is, I want to copy paste the data from other sheets in column E. I want a code which allows pasting of values which are listed in drop down values only. If I paste non listed value, then it should run the macro and show error.
for example, in list I have "Customer", but if i copy "Customers" from other sheet, then it should not be accepted in paste. but if i copy customer, then it can allow paste.
multiple rows can be pasted at a time.
if u see my sheet, i already have a code in column C which allows to paste 15 character length only. if pasted value is <> 15 then it is deleted.

 

[mumps]

@Fluff: No apologies necessary. Been there, done that!!! (Many times) @NonStopLeo: The values in your current drop down list do not have 15 characters. Are you saying that you want to accept any current value if selected from the drop down list, but if they are pasted from somewhere else, the length of each value must be exactly 15 or can it be 15 character's or less?

 

[NonStopLeo]

@Fluff: No apologies necessary. Been there, done that!!! (Many times) @NonStopLeo: The values in your current drop down list do not have 15 characters. Are you saying that you want to accept any current value if selected from the drop down list, but if they are pasted from somewhere else, the length of each value must be exactly 15 or can it be 15 character's or less?

No no no...things are mixed up...
I have code in column c, which is working fine i.e whenever i copy from anywhere and paste in column C, it keeps the values which has 15 characters and delete all others.

Now come to column E, here I have a drop down list. I can select any value in this column from drop down list. also i can copy data from somewhere else and paste in this column E. currently if I paste in column E, it is also accepting values which r in the drop down list and even also accepting thos values which are not in the list., Now i need a code which which scan the column after pasting, and if there is any value pasted which doesnt belong to the list, that should be deleted and error message should appear.

hope I have explained it well.

 

[mumps]

I think I understand now. Click here to download your file. You have 2 Worksheet_Change macros so they have to be combined into one because Excel allows only one of this type of macro per worksheet. I have added Sheet2 which contains the source data for the drop down list in column E of Sheet1. This was necessary because the macro refers to this list to compare whatever values you paste into column E.