Let me begin by saying I am new to VBA. I have research for almost two days now and i have not yet found an answer to my question. I am making a template for over 500 users. This template is for asset management. My template contains data validation. I realized that if a user was to copy and paste something from another spreadsheet into the one with data validation, it destorys the data validation. I did some research and I found this script.

Private Sub Worksheet_Change(ByVal Target As Range)
'Does the validation range still have validation?
If HasValidation(Range("ValidationRange")) 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
' 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


While this VBA works, it only works when my sheet is unprotected. I want to prevent users from removing / adding columns in my template, so I have my sheet protected. But if I have my sheet protected, this vba doesn't run and users are able to copy / paste into columns that have data validation. Please Please Please Help!!!!