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
1. |
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. |
2. |
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 |
