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?
to
but I'm getting the message Application-defined or object-defined error. Could anyone please help me how i can rewrite the code?
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
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?