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. My sample excel I am using the following code

Is the validation applied to all sheets or some sheets or just one sheet? — Siddharth Rout, Sep 24 '13 at 09:05
@SiddharthRout The validation is applied to all the sheets. And this code is written under ThisWorkBook tab. — MaheshVarma, Sep 24 '13 at 09:25
So if you see the message for for 1 sheet then you don't want the same message box the 2nd sheet? — Siddharth Rout, Sep 24 '13 at 09:26
@SiddharthRout I am sorry, It is applicable for the current sheet in the work book. My requirement is to restrict a column for predefined values from pasting into the cell which has data validation. — MaheshVarma, Sep 24 '13 at 09:30
I already have my answer ready. Just need to tweak it based on your comments. So please confirm if you need the message box to show once for all sheets or just for one relevant sheet? — Siddharth Rout, Sep 24 '13 at 09:39
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/37930/discussion-between-siddharth-rout-and-maheshvarma) — Siddharth Rout, Sep 24 '13 at 09:48

score 1 · Accepted Answer · edited May 23 '17 at 12:01

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.

enter image description here

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

edited May 23 '17 at 12:01

Community

1
1

answered Sep 24 '13 at 09:53

Siddharth Rout

147,039
17
206
250

Using the above code we can able to copy from one data validation cell to another cell which has data validation. For example I have a data validation column 'B' with different set of values (In drop down )and column 'I' with another set of values (In drop down) if I copy column 'I' cell data to column B's cell, The data in the column B's cell is being overridden with cell value of column 'I'. @siddharthrout – MaheshVarma Sep 24 '13 at 10:17
You can use INTERSECT method to check if the user is trying to write in specific columns. – Siddharth Rout Sep 24 '13 at 10:41
I am a newbie to VBA, But as part of my work I got a requirement where I should restrict the values in a cell only from the drop-down, as shown in the picture (Updated question). – MaheshVarma Sep 24 '13 at 11:02
I am a newbie to VBA, But as part of my work I got a requirement where I should restrict the value in a cell only from the list of values available in the drop-down,(copy, paste should not work in the cell which has data validation) as shown in the picture (Updated question). @SiddharthRout – MaheshVarma Sep 24 '13 at 11:09
`I am a newbie to VBA` What you are trying to achieve then is slightly complicated. Let me think of a better way to explain it to you. – Siddharth Rout Sep 24 '13 at 11:21
If I am to rephrase then what you are trying to do is disallowing paste in the relevant columns but allow the user to select the value from the data validation... correct? – Siddharth Rout Sep 24 '13 at 11:22
Yes you are right, but if the pasted value is from the list of vlaues in the drop-down, then it should allow. @SiddharthRout – MaheshVarma Sep 24 '13 at 11:25
Now you are making it even more complicated :) So now you need an extra code to check if the pasted value is in the drop down or not... Since you are learning, I would recommend taking it step by step. To disable pasting, here is what will get you going. http://www.siddharthrout.com/2011/08/15/vba-excelallow-paste-special-only/ Once you get this correctly then we will go to the next step. – Siddharth Rout Sep 24 '13 at 11:27
Before you come up with a new question in this thread it self, i would recommend breaking your question in several parts and post it as separate threads. Else this thread will never be closed and you will keep on asking new questions :) – Siddharth Rout Sep 24 '13 at 11:29
The code in the question works fine but, The error message is being displayed as many times as the number of columns with validation – MaheshVarma Sep 24 '13 at 11:32
If you are using the boolean variable then it shouldn't. – Siddharth Rout Sep 24 '13 at 11:34

Avoid multiple error pop up messages in excel

1 Answers1

Linked