CODEWITHSUNDEEP
vbaexcellistboxms-office
Tibo
Tibo

Reputation: 393

VBA : Disabling Listbox while macro is running

Problem

I have a macro (I'll call it launch_macro) which is launched by double-clicking in an Userform ListBox (ListBox1_DblClick).

My problem is that if the user double-click again while the macro is still running, the macro will be launched again as soon as the first execution is finished, regardless of the fact that I'm disabling 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

It seems like Excel records/queues the ListBox1_DblClick events (for future execution) while the associated ListBox is disabled. Why that ? How can I prevent this ?

I also tried with no success :

Note : I'm using Office Standard 2013

Current 'solution'

This trick is adapted (to reduce delay) from A.S.H answer :

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

It 'does the trick' but but I still don't like that ListBox1 is still enabled and Excel is still queueing events, thus I need to estimate how many time the user might Dbl_Click (depending on how long the macro takes) to estimate how much a delay I need (currently 0.5s to be able to handle (and log) at least 10 canceled events). Also, it seems like Excel doesn't really like (in regards to performance) queuing events while the macro is running.

Upvotes: 4

Views: 2131

Answers (2)

steve_cdi
steve_cdi

Reputation: 166

You could use a variable to lock the critical section of code for a set amount of time. The example below locks the critical part of Test() function in Sheet2 of an Excel workbook.

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

Upvotes: 0

A.S.H
A.S.H

Reputation: 29332

Well I will post my suggestion, I hope you try it because may be it was misunderstood. The idea is that once the macro is finished, we set a delay of n seconds (say 2 seconds) before handling again the double-click event. This way, the dbl-clicks that were queued during the macro's execution are handled with no effect during these two seconds.

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

Upvotes: 3

Related Questions