• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data validation prevention

T

Tom22

Member
Hi,

I am looking for a solution where i want user to select vlaues from droplist and not copy n paste something else own their on.

In one cell i have data validation so user should select from this list only , not paste something else.

How can we prevent user to do so.

I have read somewhere which says it is not possible without VBA, and i have one code with me.

This code is working fine for this workbook only...but if i tried to copy and paste from other workbook it allows it to do so.

How can we make it error proof from this glitch.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    'Does the validation range still have validation?
    If HasValidation(Range("DataValidationRange")) Then
        Exit Sub
    Else
        Application.Undo
        MsgBox "Error: You cannot paste data into these cells." & _
        "Please use the drop-down to enter data instead.", vbCritical
    End If
End Sub

Private Function HasValidation(r) As Boolean
    'Returns True if every cell in Range r uses Data Validation
    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function
 
You must log in or register to reply here.
Back
Top