VBA: disabling Listbox while running macro

Question

Problem

I have a macro (I will name it launch_macro) that is launched by double-clicking in Userform ListBox ( ListBox1_DblClick).

My problem is that if the user double-clicks again while the macro is still running, the macro will be run again as soon as the first run finishes, no matter what I turn off the ListBox while the macro is running.

Code and tests

Private sub ListBox1_DblClick(Byval Cancel as MSForms.ReturnBoolean)

   (....Logging...)

    If Not Cancel Then
        Me.ListBox1.Enabled = False
        (...DisplayStatusBar / ScreenUpdating / ListBox1.BackColor...)
        launch_macro
        (...DisplayStatusBar / ScreenUpdating / ListBox1.BackColor...)
        Me.ListBox1.Enabled = True

    End If

End sub

Excel seems to be recording / pausing ListBox1_DblClick events (for future execution) and the associated ListBox is disabled . Why is that? How can I prevent this?

I also tried without success:

• Blocked by :Me.ListBox1.Locked = True
• Domains : add DoEventsafterMe.ListBox1.Enabled = False
• EnableEvents :Application.EnableEvents = False
• macroLaunched: , (macroLaunched = True ListBox1_DblClick macroLaunched = False ). , ( , False). ( False Dbl_Click , ( ).
• ( ): 10 (Application.Wait) _. 1 . . : ListBox1.Dbl_Click "" Excel 12s .

: Office Standard 2013

""

( ) A.S.H:

Private sub ListBox1_DblClick(Byval Cancel as MSForms.ReturnBoolean)
   Static nextTime As Single

   If Timer < nextTime then
        Log_macro "Event canceled because Timer < nextTime : " & Timer
        Exit Sub
   End if

   (....Logging...)

    If Not Cancel Then
        (...DisplayStatusBar / ScreenUpdating / ListBox1.BackColor...)
        launch_macro
        (...DisplayStatusBar / ScreenUpdating / ListBox1.BackColor...)

    End If

    nextTime = Timer + 0.5
    Log_macro "nextTime = " & nextTime

End sub

, , ListBox1 - , Excel - , , Dbl_Click ( , ) , ( 0,5 ( ) 10 ). , , Excel ( ) .

Answer 1

, , , , , , . , , n (, 2 ), . , dbl-, , .

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Static NextTime As Variant ' Will set a barrier for launching again the macro 
    If Not IsEmpty(NextTime) Then If Now < NextTime Then Exit Sub

    ListBox1.Enabled = False
    ' Any event code
    launch_macro
    ' ...
    ListBox1.Enabled = True

    NextTime = Now + TimeSerial(0, 0, 2) ' dbl-click events will have no effects during next 2 seconds
End Sub

Answer 2

, . Test() Sheet2 Excel.

Option Explicit
Private booIsRunning As Boolean
Private Sub Test()

    If Not booIsRunning Then
        booIsRunning = True
        Debug.Print "Hello."
        Application.OnTime Now + TimeValue("00:00:02"), "Sheet2.UnlockTest"
    End If
End Sub

Public Sub UnlockTest()
    booIsRunning = False
End Sub