excel - 如何在 SelectionChange 之前触发 EXCEL 事件 BeforeDoubleClick？

Question

我要火灾 BeforeDoubleClick 事件 之前 EXCEL 工作表的 SelectionChange 事件。

顺序通常是相反的:选择更改事件 第一次，后来 BeforeDoubleClick-事件 .

我的目标是要么 运行 MyDoubleClickCode，如果有双击，或 如果不是，请运行 MyChangeSelectionCode。

问题取决于事件触发的顺序!

我的 最佳 解决方案来了:

    ' This Event is **MAYBE** fired secondly and runs the MyDoubleClickCode
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        dblFlag = true
        ...
        MyDoubleClickCode
        ...
    End Sub

    ' This event is always fired AND runs first
    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
        dblFlag = false
        alertTime = Now + TimeValue("00:00:01")
        Application.OnTime alertTime, "MyChangeSelectionSub"
    End Sub

    ' Userdefined subroutine which will run one second after an event ( doubleclick or not).
    public sub MyChangeSelectionSub() 
        If NOT dblFlag then
            ...
            MyChangeSelectionCode
            ...
        End if
    End Sub

我用 准时在我的 SelectionChange-event 中调用 MyChangeSelectionSub 一秒后触发选择更改。这给了处理 BeforeDoubleClick 事件和执行 MyDoubleClickCode 的时间 - 如果单元格也被双击。达到了我想要的逻辑，但是...

……当然很笨拙，不满意:我要等 一秒在 MyChangeSelectionSub 开始之前，而不是 就在 之后已经处理了 BeforeDoubleClick 事件。

Maybee有一种 逻辑 让这发生？任何想法？

编辑 :我已经编辑了代码示例，以便更清楚地了解我的问题!而且我现在知道我无法更改事件的顺序，但是如何不使用 onTime 解决方案？

Answer 1

这对我“有效”，但似乎不稳定。 OnTime 方法的计时可能会导致我们可能需要接受的执行中的“不舒服的暂停”。 (或改进。)

'worksheet (Name) is "Sheet17" in the VBA Properties window
'worksheet Name is "Sheet1" as shown in the worksheet tab in the application Excel

Private double_click_detected As Boolean
Private SelectionChange_target As Range

' This Event is **MAYBE** fired secondly and runs the MyDoubleClickCode
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  double_click_detected = True
  '...
  MsgBox "MyDoubleClickCode"
  '...
End Sub

' This event is always fired AND runs first
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Set SelectionChange_target = Target
    alertTime = Now + TimeValue("00:00:01")
    Application.OnTime alertTime, "Sheet17.MyChangeSelectionSub"
End Sub

' Userdefined subroutine which will run one second after an event ( doubleclick or not).
Public Sub MyChangeSelectionSub()
  If Not double_click_detected Then
    '...
    MsgBox "MyChangeSelectionCode"
    '...
  End If
End Sub