Prevent pasting over data validation

[antho27]

I am trying to secure a worksheet using data validation. I want to prevent users pasting over data validation fields, however I want to allow users to be able to still use the copy/paste feature as long as the data is valid.

Attached is a sample that I found on the forums. The current code does not allow any paste functionality.

Any help is appreciated.

Regards,
Anthony

[AlphaFrog]

This lets the user paste over data validation. Then it validates their pasted values and restores the data validation lists in the pasted cells.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim cell As Range, bHasValidation As Boolean
    
    If Not Intersect(Target, Range("A1:A10")) Is Nothing Then
    
        Application.ScreenUpdating = False
        
        For Each cell In Intersect(Target, Range("A1:A10"))
            
            On Error Resume Next
                bHasValidation = cell.Validation.Type = xlValidateList
            On Error GoTo 0
            If Not bHasValidation Then
            
                'Validate pasted cells
                If Range("$F$1:$F$10").Find(cell.Value, , xlValues, xlWhole, , , False) Is Nothing Then
                    Application.EnableEvents = False
                    cell.Value = ""
                    Application.EnableEvents = True
                End If
                
                'Restore validation lists
                With cell.Validation
                    .Add Type:=xlValidateList, _
                         AlertStyle:=xlValidAlertStop, _
                         Operator:=xlBetween, _
                         Formula1:="=$F$1:$F$10"
                    .IgnoreBlank = True
                    .InCellDropdown = True
                    .InputTitle = ""
                    .ErrorTitle = ""
                    .InputMessage = ""
                    .ErrorMessage = ""
                    .ShowInput = True
                    .ShowError = True
                End With
            End If
        Next cell
        
        Application.ScreenUpdating = True
        
    End If
    
End Sub

[antho27]

wow thank you!! This is great.

Is there a way to add a message box if the data is not valid? or prevent pasting special values?