Hello all,
I am trying to prevent copy/paste over data validation. I have the following code and it works perfect. I can not run a loop because I have different columns for data validations. I can not select a non-contigious range neither because the formula doesn't allow me to do so when I try make changes in drop down list. Is there a way to shorten the following code?
Thanks in advance,
I am trying to prevent copy/paste over data validation. I have the following code and it works perfect. I can not run a loop because I have different columns for data validations. I can not select a non-contigious range neither because the formula doesn't allow me to do so when I try make changes in drop down list. Is there a way to shorten the following code?
Code:
Select Case Target.Column
Case Is = 3
Set Rng1 = Sheets("NCR Log").Range("C4:C1048576")
If HasValidation(Rng1) Then
'do nothing
Else
Application.EnableEvents = False
MsgBox "Your last operation was canceled." & vbNewLine & _
"It would have violated data validation rules.", vbCritical
Application.Undo
Application.EnableEvents = True
End If
Case Is = 4
Set Rng2 = Sheets("NCR Log").Range("D4:D1048576")
If HasValidation(Rng2) Then
'do nothing
Else
Application.EnableEvents = False
MsgBox "Your last operation was canceled." & vbNewLine & _
"It would have violated data validation rules.", vbCritical
Application.Undo
Application.EnableEvents = True
End If
Case Is = 5
Set Rng3 = Sheets("NCR Log").Range("E4:E1048576")
If HasValidation(Rng3) Then
'do nothing
Else
Application.EnableEvents = False
MsgBox "Your last operation was canceled." & vbNewLine & _
"It would have violated data validation rules.", vbCritical
Application.Undo
Application.EnableEvents = True
End If
Case Is = 6
Set Rng4 = Sheets("NCR Log").Range("F4:F1048576")
If HasValidation(Rng4) Then
'do nothing
Else
Application.EnableEvents = False
MsgBox "Your last operation was canceled." & vbNewLine & _
"It would have violated data validation rules.", vbCritical
Application.Undo
Application.EnableEvents = True
End If
Case Is = 10
Set Rng5 = Sheets("NCR Log").Range("J4:J1048576")
If HasValidation(Rng5) Then
'do nothing
Else
Application.EnableEvents = False
MsgBox "Your last operation was canceled." & vbNewLine & _
"It would have violated data validation rules.", vbCritical
Application.Undo
Application.EnableEvents = True
End If
Thanks in advance,