Avoid multiple error pop up messages in excel

Question

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    'Does the validation range still have validation?
    If Not HasValidation(Range("A1:A1048576")) Then RestoreValidation
    If Not HasValidation(Range("C1:C1048576")) Then RestoreValidation
    If Not HasValidation(Range("I1:I1048576")) Then RestoreValidation
    If Not HasValidation(Range("P1:P1048576")) Then RestoreValidation
End Sub

Private Sub RestoreValidation()
    Application.EnableEvents = False
    'turn off events so this routine is not continuously fired
    Application.Undo
    Application.EnableEvents = True
    'and turn them on again so we can catch the change next time
    MsgBox "Your last operation was canceled." & _
    "It would have deleted data validation rules.", vbCritical
End Sub

Private Function HasValidation(r) As Boolean
    '   Returns True if every cell in Range r uses Data Validation
    On Error Resume Next
    Debug.Print r.Validation.Type    'don't care about result, just possible error
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

I applied validation on 4 columns with the above code, Even the validation is passed I am getting 4 error pop up messages how to restrict number of error messages ?

UPDATE:

I selected the value from the drop down which is a valid selection, but I am getting the below error message. I am using the following code

Answer 1

If you are working with the sheet's Change event, then I would recommend having a look at THIS

Since you are working with just one sheet then you don't need the code in the ThisWorkbook code area. If you put it there then the code will run for every sheet. Put the code in the relevant sheet's code area. So if the validation is in Sheet1 then put the code in the Sheet1 code area. See ScreenShot below.

Ok now to address your query. What you can do is use a Boolean variable and then set it to True after you show the first message so that the message doesn't show again.

Try this (UNTESTED)

Dim boolDontShowAgain As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    Application.EnableEvents = False

    If Not HasValidation(Range("A1:A1048576")) Then RestoreValidation
    If Not HasValidation(Range("C1:C1048576")) Then RestoreValidation
    If Not HasValidation(Range("I1:I1048576")) Then RestoreValidation
    If Not HasValidation(Range("P1:P1048576")) Then RestoreValidation

Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

Private Sub RestoreValidation()
    Application.Undo
    If boolDontShowAgain = False Then
        MsgBox "Your last operation was canceled." & _
        "It would have deleted data validation rules.", vbCritical
        boolDontShowAgain = True
    End If
End Sub

Private Function HasValidation(r) As Boolean
    On Error Resume Next
    Debug.Print r.Validation.Type
    If Err.Number = 0 Then HasValidation = True
End Function