Excel VBA 数据验证检查表中的数据验证是否失败

Question

下面的代码有两个错误。

首先，它检查工作表中所有单元格的数据验证，即使这些单元格位于表格的 DataBodyRange 之外，并显示 MsgBox“失败”。

其次，即使数据验证尚未应用于表格 DataBodyRange 内的单元格，它也会显示 MsgBox“失败”。

我该如何编辑它以便它只检查 Changed\ActiveCell 是否在表的 DataBodyRange 内，并且只有在应用了数据验证时才显示 MsgBox“失败”，否则忽略它？

Public Sub Worksheet_Change(ByVal Target As Range)
Call DataValidation
End Sub

Public Sub DataValidation()
Dim wb As Workbook
Dim ws As Worksheet
Dim tbl As ListObject
Dim rng As Range
Dim cell As Range

Set wb = ThisWorkbook
Set ws = wb.ActiveSheet
Set tbl = ws.ListObjects(1)
Set rng = tbl.DataBodyRange

For Each cell In rng
    If Not cell.Validation.Value Then
        MsgBox "Failed"
    End If
Next cell
End Sub

编辑

我尝试修改此代码，但数据验证失败时不会显示 Msgbox Failed

Dim wb As Workbook
Dim ws As Worksheet
Dim tbl As ListObject
Dim rng As Range
Dim ac As Range

Set wb = ThisWorkbook
Set ws = wb.ActiveSheet
Set tbl = ws.ListObjects(1)
Set rng = tbl.DataBodyRange
Set ac = ActiveCell

If Not Intersect(ac, rng) Is Nothing Then
    If Not ac.Validation Is Nothing Then
        If Not ac.Validation.Value Then
            MsgBox "Failed"
        End If
    End If
End If