Prevent pasting into data validation cells in multiple columns

[Michael Baker]

Hi,
I am very new to VBA code, and have been using it for just this reason. If you can help I would be delighted as I have been stuck for a day now.

I have used the code below, which works fine when only one range is specified. However, when I try to apply it to multiple ranges by using;

If HasValidation(Range("ValidationRange1,ValidationRange2,ValidationRange3")) Then

It fails and a runtime error 28 is encountered. I think I am just specifying the ranges incorrectly. Can you help?

Private Sub Worksheet_Change(ByVal Target As Range)
'Does the validation range still have validation?
If HasValidation(Range("ValidationRange")) Then
Exit Sub
Else
Application.Undo
MsgBox "Your last operation was canceled." & _
"It would have deleted data validation rules.", 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

 

[NARAYANK991]

Hi Michael ,

I am not sure this will work , but can you try :

If HasValidation(Union(Range("ValidationRange1"),Range("ValidationRange2"),Range("ValidationRange3"))) Then

Narayan

 

[Michael Baker]

Hi Narayan,

Thanks for this but I get error code 28 when I use;

If HasValidation(Union(Range("ValidationRange1"),Range("ValidationRange2"),Range("ValidationRange3"))) Then

 

[NARAYANK991]

Hi Michael ,

Can you try the following code ?

Private Sub Worksheet_Change(ByVal Target As Range)
    'Does the validation range still have validation?
    If HasValidation(Union(Range("ValidationRange"), Range("ValidationRange1"), Range("ValidationRange2"))) Then
        Exit Sub
    Else
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
        MsgBox "Your last operation was canceled." & _
        "It would have deleted data validation rules.", 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
    HasValidation = True
    If r.Areas.Count > 1 Then
      For Each ar In r.Areas
          x = ar.Validation.Type
          If Err.Number <> 0 Then HasValidation = False
      Next
    Else
      x = r.Validation.Type
      If Err.Number = 0 Then HasValidation = True Else HasValidation = False
    End If
End Function

Narayan