vba - 数据验证和工作表更改事件

Question

当使用工作簿激活事件打开工作簿时，我使用 VBA 宏来查询数据库并构建可用项目列表。我有项目编号和项目名称，它们组合成两个单独的数据验证列表并应用于两个单元格。工作表更改事件测试这些单元格中的更改，将其数据验证列表拆分为数组，并从另一个数组中选择相应的项目信息。例如，如果我选择一个项目编号，工作表更改事件会查找该项目编号在项目编号数组中的位置，然后根据位置从名称数组中选取项目名称。

每当从下拉列表中选择一个值时，这都可以完美地工作，但是当输入列表之外的值时，我会遇到问题。例如，如果我输入一个空白单元格，我可能会收到数据验证错误，或者当我使用 match 查找数组中输入的值时，可能会出现类型不匹配的情况。我有一个错误处理程序来处理类型不匹配，但我希望每次都会触发数据验证错误。另一个问题是事件有时会被禁用。这要严重得多，因为用户无法重新打开它们。

除此之外，我无法弄清楚这是在哪里或如何发生的。我无法复制如何使用中断禁用事件，因为复制导致使用中断禁用事件的步骤只会导致我的错误处理程序。但是，当未应用中断时，错误处理程序有时将无法触发，并且事件将被禁用。由于我在解析数组之前禁用事件，因此我认为工作表更改在 Loc=Application.Match(Target.Text, NumArr, 0) - 1 行失败，但我可以不明白为什么不会触发错误。至少，我应该收到一条包含错误号和描述的消息，并且应该重新启用事件。

任何人都可以就工作表更改和数据验证之间的交互提供建议吗？这里的调用顺序是什么？还有其他建议吗？我还缺少什么吗？

预计到达时间:我已经用 Google 搜索过此内容，但没有找到任何有帮助的信息。出现的一切都是关于将数据验证工作到工作表更改中，而不是关于交互或调用顺序。

ETA #2:在尝试了下面答案中的实验后(感谢加里的学生)，这变得有点奇怪。如果我选择“重试”并选择旧的默认值，我会获得旧值三次。如果我点击删除，我会在消息框中看到一个空格，但只有一个消息框。然后单元格留空。我可以通过单击“重试”并接受空格将 DV 放入循环中。 DV 错误将会出现，直到我单击“取消”。然后我将得到一系列空的文本消息框，每次我重试空单元格时都会出现一个。如果我从列出的值开始，使用退格键清除单元格，单击“重试”，然后尝试选择另一个值，则工作表更改事件在相交 3 次时失败。我认为下面的答案更能说明正在发生的事情，但它也确实提出了更多问题。

这是我的代码:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim NumArr() As String
Dim ProjArr() As String
Dim Loc As Integer

On Error GoTo ErrHandler:

If Target.Address = "$E$4" Then
    'Disable events to prevent worksheet change trigger on cell upates
    Application.EnableEvents = False

    'Parse validation lists to arrays
    NumArr = Split(Target.Validation.Formula1, ",")
    ProjArr = Split(Target.Offset(1, 0).Validation.Formula1, ",")

    'Change error handler
    On Error GoTo SpaceHandler:

    'Determine project number location in array
    Loc = Application.Match(Target.Text, NumArr, 0) - 1

    'Change error handler
    On Error GoTo ErrHandler:

    'Change cell value to corresponding project name based on array location
    Target.Offset(1, 0) = ProjArr(Loc)

    'Unlock cells to prepare for editing, reset any previously imported codes
    Range("C8:G32").Locked = False

    'Run revenue code import
    RevenueCodeCollector.ImportRevenueCodes

    'Re-enable events
    Application.EnableEvents = True

End If

If Target.Address = "$E$5" Then

    Application.EnableEvents = False

    NumArr = Split(Target.Validation.Formula1, ",")
    ProjArr = Split(Target.Offset(-1, 0).Validation.Formula1, ",")
    Loc = Application.Match(Target.Text, NumArr, 0) - 1
    Target.Offset(-1, 0) = ProjArr(Loc)

    Range("C8:G32").Locked = False
    RevenueCodeCollector.ImportRevenueCodes
    Application.EnableEvents = True

End If

Exit Sub

ErrHandler:
MsgBox Err.Number & " " & Err.Description
Application.EnableEvents = True
Exit Sub

SpaceHandler:
MsgBox "Pick a project from the dropdown.", vbOKOnly, "Error"
Application.EnableEvents = True

End Sub

Answer 1

你有一个非常开放式的问题……没有时间做完整的白皮书，这里有一个简单的实验。我使用事件代码:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim A1 As Range, rINT As Range

    Set A1 = Range("A1")
    Set rINT = Intersect(A1, Target)
    If rINT Is Nothing Then Exit Sub
    MsgBox A1.Value
End Sub

在A1中，我按如下方式设置DV:

如果我使用下拉菜单，我会得到输入的值，并且还会得到 MsgBox。但是，如果我单击单元格并输入一些垃圾内容，会发生什么:

1. 出现DV警报，我触摸了“取消”按钮
2. 我收到 2 MsgBox 次出现，每次都包含原始内容而不是尝试的垃圾内容!!

我完全不知道为什么会引发该事件，因为单元格实际上并未更改，更不用说为什么会引发该事件两次!几乎就像该事件在垃圾条目上引发，但 DV 警报优先，DV 反转条目并引发另一个事件，最后两个事件都得到处理。

希望比我聪明的人能插话。