Hi all,
I found this code off
in order to do what i wanted, which is to restrict people from pasting over and thus deleting my data validation in a shared workbook, however an error comes up if I tried to set the DataValidationRange to include multiple column with different data validation (some validations are list, some are date and so on).
So I tried to set up a separate instant for each column I have (Name column A as DataValidationRange1, Column C with a different data validation as DataValidationRange2) and edited the code as so to include two If scenario, but an error still comes up, not sure why. Can Anyone help advise me on this?
I found this code off
HTML:
https://superuser.com/questions/870926/restrict-paste-into-dropdown-cells-in-excel?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa
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
So I tried to set up a separate instant for each column I have (Name column A as DataValidationRange1, Column C with a different data validation as DataValidationRange2) and edited the code as so to include two If scenario, but an error still comes up, not sure why. Can Anyone help advise me on this?
Code:
Private Sub Worksheet_Change(ByVal Target As Range) 'Does the validation range still have validation?
If HasValidation(Range("DataValidationRange1")) 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
If HasValidation(Range("DataValidationRange2")) 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