避免在excel中出现多个错误弹出消息 [英] Avoid multiple error pop up messages in excel

问题描述

$ b Private Sub Workbook_SheetChange（ByVal Sh As Object，ByVal Target As Range）
'验证范围是否仍然有验证？
如果Not HasValidation（范围（A1：A1048576））然后RestoreValidation
如果不是hasValidation（范围（C1：C1048576））然后RestoreValidation
如果不是hasValidation（范围（I1 ：I1048576））然后RestoreValidation
如果不是hasValidation（范围（P1：P1048576））然后RestoreValidation
End Sub

私有Sub RestoreValidation（）
应用程序.EnableEvents = False
'关闭事件，所以这个例程不是不断地被触发
Application.Undo
Application.EnableEvents = True
'并重新打开它们，所以我们可以抓住下次更改
MsgBox您的最后一次操作已取消。 &安培; _
它将删除数据验证规则，vbCritical
End Sub

私有函数HasValidation（r）As Boolean
'如果每个单元格范围r使用数据验证
错误恢复下一步
Debug.Print r.Validation.Type'不关心结果，只是可能的错误
如果Err.Number = 0 Then HasValidation = True Else HasValidation = False
结束函数

我用上面的代码对4列进行了验证，即使是验证通过我收到错误的弹出消息如何限制错误消息的数量？

更新：

我从下拉菜单中选择了一个有效选择的值，但是我收到以下错误消息。
我使用以下代码

解决方案

如果您正在使用工作表的更改事件，那么建议您查看THIS

由于您只使用一张表，所以您不需要 ThisWorkbook 代码区中的代码。如果你把它放在那里，代码将会运行在每个工作表中。将代码放在相关工作表的代码区中。因此，如果验证在 Sheet1 中，则将代码放在 Sheet1 代码区域中。请参阅下面的ScreenShot。

现在确定要解决您的查询。您可以使用 Boolean 变量，然后在显示第一条消息后将其设置为 True ，以便消息不再显示。

尝试这个（UNTESTED）

  Dim boolDontShowAgain As Boolean 
 
 Private Sub Worksheet_Change（ByVal Target As Range）
错误GoTo Whoa 
 
 Application.EnableEvents = False 
 
如果Not HasValidation（范围（A1：A1048576））然后RestoreValidation 
如果不是hasValidation（范围（C1：C1048576））然后RestoreValidation 
如果不是hasValidation（范围（I1：I1048576 ））然后RestoreValidation 
如果没有HasValidation（范围（P1：P1048576））然后RestoreValidation 
 
 Letscontinue：
 Application.EnableEvents = True 
退出Sub 
哇：
 MsgBox Err.Description 
 Resume Letscontinue 
 End Sub 
 
 Private Sub RestoreValidation（）
 Application.Undo 
如果boolDontShowAgain = False然后
 MsgBox您最后一次操作已被取消。 &安培; _ 
它将删除数据验证规则，vbCritical 
 boolDontShowAgain = True 
 End If 
 End Sub 
 
私有函数HasValidation（r） As Boolean 
 On Error Resume Next 
 Debug.Print r.Validation.Type 
如果Err.Number = 0 Then HasValidation = True 
结束函数
  

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

解决方案

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

这篇关于避免在excel中出现多个错误弹出消息的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！