Only Allow Copy Paste for Cells with Data Validation

JVRamoso02

New Member
Joined
Apr 29, 2016
Messages
4
Hi Everyone,

I found this macro which allows me to only allow copy-paste values in order for data validation to work. However, this is only one cell. How can i convert to make it work for a range of cells?

Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    Dim IsDV As Boolean
    If Target.Address(0, 0) = "A1" Then
        On Error Resume Next
            IsDV = Target.SpecialCells(xlCellTypeAllValidation).Cells.Count > 0
        On Error GoTo 0
        Application.EnableEvents = False
        On Error GoTo ReEnable
        If IsDV Then
            If Evaluate(Target.Validation.Formula1).Find(Target, , , 1, , , 0) Is Nothing Then
                MsgBox "Cannot paste values that are not within the list. ", vbExclamation, "Invalid Entry"
                Application.Undo    'Not within the DV list
            End If
        Else
            MsgBox "Cannot paste over the data validation cell. ", vbExclamation, "Invalid Paste"
            Application.Undo    'Pasted over the DV cell
        End If
    End If
ReEnable:
    Application.EnableEvents = True
    If Err.Number <> 0 Then
        MsgBox "Error " & Err.Number & vbLf & Err.Description, _
               vbCritical, "Worksheet_Change Procedure Error"
        Err.Number = 0
    End If
End Sub



I tried changing the code
[CODE]If Target.Address(0, 0) = "A1" Then
to

Code:
If Target.Count > 1 Then Exit Sub
If Not Intersect(Range("DVLists"), Target) Is Nothing Then

but I'm getting the message Application-defined or object-defined error. Could anyone please help me how i can rewrite the code?
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top