I took the answer to
the linked question “Restrict paste into dropdown cells in Excel”
and adapted it for multiple ranges, and made some cosmetic changes.
I also added a safety net to catch recursion.
You may be able to delete
the bold/italic lines:
Dim WSC_Recursive As Boolean
Private Sub Worksheet_Change(ByVal Target As Range)
'Does the validation range still have validation?
If WSC_Recursive Then
If HasValidation(Range("DataValidationRange1")) And _
HasValidation(Range("DataValidationRange2")) And _
HasValidation(Range("DataValidationRange3")) And _
WSC_Recursive = True
WSC_Recursive = False
MsgBox "Error: You cannot paste data into these cells." & vbCrLf & _
"Please use the drop-down to enter data instead.", vbCritical
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
Note: each of the NamedRanges must have validation applied to it — in fact,
it must have the same validation applied to every cell in the range.
For example, you cannot have a range of
G17:G42 has one type of validation and
H17:H42 has a different type
— or even if they have the same type (e.g., drop-down)
but different parameters (e.g., different lists of valid values).
If you have this set up and working, and you want to add a range, you should:
In either order,
- define the named range and
- apply the validation to the cells
and only then
add the new range to the
In the more general case where you’re changing validation rules,
you might want to follow this procedure:
- Rename the
Worksheet_Change routine to something else (e.g.,
- In either order,
- Rename the routine back to
Personally, I believe that this code could be designed better,
but, if it works, it works.