Drop Down protection

Hi Warren,

This has been a problem since Excel began! Pasting values over the top will remove data validation. If a cell can be edited, then pasting can break it. This is due to Excel functioning as a data analytical programme not as a Database program such as Access.

There is a work around using VBA code to constantly check the sheet for changes.

It would look something like this

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


Kind regards

Richard Bailey
Microsoft Certified Trainer

Best STL - https://www.stl-training.co.uk
98%+ recommend us

London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector