With Copy Paste over Data Validation cell , its not working

Question

I set up a excel sheet with drop-down menus for some of the cells. The user can only select values from that list and an error message pops up when something is typed it that is not in the list (via Data Validation

Error Alert).

So this works all fine ... But when the user copy paste into the cells then validation doesnt work. How to make validation effective in case of copy paste. I have searched and found one solution but its not working. Here is the code that I have found. but its not working any more..It always return true enven I copy paste worng

Private Sub Worksheet_Change(ByVal Target As Range)

   If HasValidation(Range(ActiveCell.Address)) Then
        Exit Sub
    Else
        Application.Undo
        MsgBox "Your last operation was canceled." & _
        "It would have deleted data validation rules.", vbCritical

    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

JimmyPena JimmyPena · Accepted Answer · 2011-11-14T16:45:28

It looks like you took the code from this page:

http://www.j-walk.com/ss/excel/tips/tip98.htm

When Worksheet_Change fires, your code always refers to the active cell, not the target where the copy/paste operation is being performed.

Change

ActiveCell.Address

to

Target.Address

and see if that works.

With Copy Paste over Data Validation cell , its not working

2 Answers