Prevent Copy Paste on 2 separate Data Validation ranges

[DaveG]

I've set up a spreadsheet with data validation and want to stop copy and paste. I can get this to work fine for a single column of data, however I have different data validation rules in different columns, and I can't find a way of stopping copy and paste across both columns. I am using the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    'Do the validation ranges still have validation?
   
   If HasValidation(Range("ValR1", "ValR2")) Then
        Exit Sub
    Else
        Application.EnableEvents = False
        Application.Undo
        MsgBox "Your last operation was canceled." & _
        "It would have deleted data validation rules.", vbCritical
    End If
    Application.EnableEvents = True
       
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

The code I've given will stop copy and paste to the 2 columns, however it also throws up the error message whenever you make any changes anywhere else in the workbook. Any ideas on why that might be? If I take the ValR2 range out of the code it works as intended, but I need it to work across multiple data validation ranges. I've attached an example file in case that helps.

Thanks in advance,
Dave

 

[chirayu]

They are 2 different ranges, cant be put in same argument. Changed the code to below

Private Sub Worksheet_Change(ByVal Target As Range)
  'Do the validation ranges still have validation?
 
  If HasValidation(Range("ValR1")) And HasValidation(Range("ValR2")) Then
  Exit Sub
  Else
  Application.EnableEvents = False
  Application.Undo
  MsgBox "Your last operation was cancelled." & _
  "It would have deleted data validation rules.", vbCritical
  End If
  Application.EnableEvents = True
 
End Sub

 

[DaveG]

Works a treat, thanks for you help!