首页 文章

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

提问于
浏览 次
1

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

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

除此之外，我无法弄清楚这种情况发生在何处或如何发生 . 我可以't replicate how the Events are disabled using breaks because duplicating the steps that lead to the events being disabled with breaks in place only leads to my error handler. However, when breaks aren' t应用，错误处理程序有时无法触发，事件将被禁用 . 由于我认为工作表更改在 Loc=Application.Match(Target.Text, NumArr, 0) - 1 行失败，但我无法弄清楚为什么不会触发错误 . 至少，我应该收到包含错误号和描述的消息，并且应该重新启用事件 .

任何人都可以建议工作表更改和数据验证之间的交互吗？这里的电话订单是什么？还有其他建议吗？我错过了什么？

ETA：我用谷歌搜索过，但我没有找到任何有用的东西 . 所有出现的内容都是将数据验证工作变为工作表更改，而不是交互或调用顺序 .

ETA＃2：在下面的答案中尝试实验后（感谢Gary的学生），这有点奇怪了 . 如果我选择“重试”并选择旧的默认值，我会得到旧值三次 . 如果我点击删除，我会在消息框中找到一个空格，但只有一个消息框 . 然后将单元格留空 . 我可以通过单击“重试”并接受空格将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
excel vba excel-vba

2 回答

  • 2

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

    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如下：

    enter image description here

    如果我使用下拉菜单，我会输入值，我也会得到 MsgBox . 但是，如果我点击单元格并键入一些垃圾，会发生什么：

    • 发生DV警报，我触摸CANCEL按钮

    • 我收到 2 MsgBox 次事件，每次都有原始内容而不是尝试的垃圾！

    我完全不知道为什么事件被引发，因为单元格实际上没有被更改，更不用说为什么事件被引发 twice !!这几乎就像在垃圾进入时引发事件一样，但DV警报优先，DV反转条目并引发另一个事件，最后两个事件都被处理 .

    Hopefully a person smarter than me will chime in.

    回复于
  • 0

    通过参考查询，可以管理DV和更改事件的变通方法 .

    Public strRange As String
Public bCheck As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
If bCheck Then Exit Sub

MsgBox "Correct Entry!"

strRange = Target.Address
bCheck = True
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address <> strRange Then bCheck = False
End Sub

    http://forum.chandoo.org/threads/multiple-worksheet-change-event-with-data-validation.32750

    回复于

相关问题