Hi again,
If I replace the "Application.Undo" with the :
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
then the message box appears even if I choose something on the list and not only in copy/paste.
I tried the 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
in a xlsm with 3 columns of data validation and it didn t work. I attach a file to see the exact error message. Just choose in a blue cell a value from the list.
Warning! Better save any open workbook before because the programm stucks...
Bookmarks