Listbox Events

[douglasH]

Hello,

I setup a Listbox on a UserForm as a multi-select Listbox to enable the user to deselect any or all Listbox items. However, I only want the user to have the option of selecting one item at a time. So, I created a module level procedure to deselect all items in the Listbox when the user selects an item in the Listbox. After all items in the Listbox are deselected, the Listbox change event reselects the item the user originally chose: the user selects an item, all items in the Listbox are deselected, and then the original item is reselected. To make this work, I created a Public variable as a boolean named EnableEvents on the Listbox UserForm so that the Listbox change event will only call the Listbox deselect procedure if this value is set to True. In the deselect procedure at the module level, I set EnableEvents to false so that when the code deselecting all items in the Listbox runs, it will not fire the Listbox change event.

It actually works and is seamless to user, except when I count the number of times the change event in the Listbox fires, I get 1,286 times! The Listbox event procedure should only fire each time an item is deselected at the module level (approximately 10 times)!

Here's my code:

Form level:

Public EnableEvents As Boolean

Userform Listbox change event:

Private Sub lstCategories_Change()

Dim seldx As Integer

'MsgBox "change lstCategories"

frmControl.Label2.Caption = Val(frmControl.Label2) + 1

'Exit Sub

If frmControl.EnableEvents = True Then
seldx = frmControl.lstCategories.ListIndex
If frmControl.lstCategories.Selected(seldx) = True Then
frmControl.EnableEvents = False
seldx = frmControl.lstCategories.ListIndex
Call frmControl_lstCategories_deselect_all_categories
frmControl.lstCategories.Selected(seldx) = True
frmControl.EnableEvents = True
End If
End If

End Sub

Module deselect procedure:

Sub frmControl_lstCategories_deselect_all_categories()

Dim itmsdx As Integer

frmControl.EnableEvents = False

For itmsdx = 0 To frmControl.lstCategories.ListCount - 1
frmControl.lstCategories.Selected(itmsdx) = False
Next itmsdx

frmControl.EnableEvents = True

End Sub

***

I need help finding out why the Listbox change event is firing 1,286 times!

Thanks very much,

Doug

[HansV]

1) If you want the user to be able to select only one item, why did you make the listbox multi-select?

2) I created a userform with a listbox and a label, and copied your code. I could still select multiple items. Perhaps I did something wrong...
Could you attach a sample wrkbook (without sensitive data) that demonstrates the behavior that you describe?

[douglasH]

Hi Hans,

The reason I made the Listbox a muti-select Listbox is that I wanted the user to be able to deselect all items in the Listbox. If it is not multi-select, the user cannot deselect all items (to my knowledge).

Attached is a sample file I made.

Thanks for your help!

[HansV]

Each time you change the list box, its Change event is called, whether EnableEvents is True or not, so you get a cascade of events.
You increase the counter before checking EnableEvents, so the counter gets increased each time. The code after that will be skipped if EnableEvents is False.

I'd change the list box back to single select, and provide a command button to reset it:

Code: Select all

Private Sub CommandButton2_Click()
    Me.ListBox1.ListIndex = -1
End Sub

You can then remove the EnableEvents variable from all your code, as well as the ListBox1_Change event procedure.
See the attached version.

Listbox problem.xlsm

[douglasH]

Thanks for your help Hans! Much appreciated!