Displaying a message box after using ListBoxName.List() in a double-click event disables all other UserForm controls


Displaying a message box after using ListBoxName.List() in a double-click event disables all other UserForm controls

Article contributed by Robin Seeländer, Anna-Karin Bohman and Dave Rado

Using ListBoxName.LIst() to populate a list box is much faster in the case of large arrays than using AddItem. However, this method does have one unfortunate bug.

If, in the double-click event of a listbox, you display a message box and then repopulate the contents of the listbox from an array using ListBox1.List = ArrayName, then the user cannot click any other button in the UserForm unless they first hover with the mouse over the listbox!

The problem does not occur if you use AddItem to repopulate the listbox, rather than using the List method.

Steps to reproduce

Create a UserForm called UF, and add a listbox called LB and an OK button called cmdOK.

In the main module:

Option Explicit

Sub RunUF()
UF.Show
End Sub

In the UserForm module:

Option Explicit

Private Sub cmdOK_Click()
    Unload Me
End Sub

Private Sub LB_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim ListArray As Variant
    MsgBox "Here is a message"
    ListArray = Array("4", "5")
    LB.List() = ListArray
End Sub

Private Sub UserForm_Initialize()
Dim ListArray As Variant
    ListArray = Array("1", "2", "3", "4")
    LB.List() = ListArray
End Sub

Run the code, click OK on the message box, and then try to click OK on the UserForm – you can't!

Then hover over the list box and click OK again – now you can!

Using AddItem in the Initialize event makes no difference; it's only when repopulating a listbox after displaying a message box that the .List method causes problems.

(Note for any MS developers who may read this: strictly speaking, it isn't the message box as such that causes the problem, it's the fact that displaying the message box prevents the mouse from hovering over the listbox immediately after double-clicking. If you don't display a message box, the mouse will automatically be hovering over the list box, hence the problem doesn't arise If you use an API call to immediately set the mouse to coordinates 0,0 on the screen after copying the array to the listbox, you do get the problem without displaying a message box. Or if you displayed a second UserForm on top of the first, you would get the problem. But in real world applications, the bug is only likely to occur if you do display a message box.)

Workaround 1

One workaround is to use AddItem instead of the List method; but as previously mentioned, the List method is much faster, if it's a large array.

Workaround 2

Hide and then re-show the UserForm. This is more elegant than it might appear at first sight, because the user will not see the dialog disappear and reappear – and it's very fast. So in the above example, the following works:

Private Sub LB_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim ListArray As Variant
    MsgBox "Here is a message"
    ListArray = Array("4", "5")
    LB.List() = ListArray
    Me.Hide
    Me.Show
End Sub

Notes

The line Me.Show must be the final line in the double-click event procedure. Any code in that procedure that follows that line will not fire until you unload the UserForm.

The Activate event fires whenever you re-Show the UserForm (the Initialize event doesn't, though). So if you have written an Activate Event procedure, you will need to use a Boolean variable to prevent your Activation code from running twice, like this:

Option Explicit

Dim AlreadyActivated As Boolean

Private Sub cmdOK_Click()
    AlreadyActivated = False
    Unload Me
End Sub

Private Sub LB_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
Dim ListArray As Variant
    MsgBox "Here is a message"
    ListArray = Array("4", "5")
    LB.List() = ListArray
    Me.Hide
    Me.Show
End Sub

Private Sub UserForm_Initialize()
Dim ListArray As Variant
    ListArray = Array("1", "2", "3", "4")
    LB.List() = ListArray
End Sub

Private Sub UserForm_Activate()
    If Not AlreadyActivated Then
        'Your code here
    End If
    AlreadyActivated = True
End Sub