Restrict to single selection in Multi select Listbox

[pannai]

Hi
I have a listbox on the userform which is of multiselect type. But when the user select a particular value in the listbox, I need to put restriction such that user can not select other values in that listbox. Can anyone please help me?

Thanks in advance

[mdmackillop]

Can you not change MultiSelect to single?

[Bob Phillips]

That doesn't seem to work, nor does disabling the Listbox. These don't seem to be dynamic properties.

A question I also had before I couldn't get anything to work was, what happens if the specific item to turn off multi-select is not the first selected?

[mdmackillop]

Works for me, or am I missing something.
Here's my example (Excel 2000)

[Bob Phillips]

Malcolm,

I was reading the requirement as starting the Listbox multi-select, and then when a PARTICULAR value is selected, don't allow any second selection from that point. Not starting as single select.

[JonPeltier]

... what happens if the specific item to turn off multi-select is not the first selected?
I would check which items in the listbox were selected whenever the listbox change event fired, and if the particular item were one of them, unselect any others, then change the .multiselect property.


That doesn't seem to work, nor does disabling the Listbox.
There seems to be a problem with the display of the userform when you change the multiselect property to single, then use shift or ctrl to select an unselected item. The form knows that only the item you just selected is selected, but the list items are highlighted as if the multiselect property were still multi.

But we don't need to change the multiselect property, just not allow any other item to be selected. This code assumes a multiselect listbox on a userform, populated with items a, b, c, d. It works as a multiselect listbox until c is selected, then no matter what the user does, c stays selected and nothing else can be selected instead.


Option Explicit
Dim mbNoChanges As Boolean

Private Sub ListBox1_Change()
Dim iList As Long
If Not Application.EnableEvents Then Exit Sub
Application.EnableEvents = False
With Me.ListBox1
If Not mbNoChanges Then
For iList = 0 To .ListCount - 1
If .List(iList) = "c" And .Selected(iList) Then
mbNoChanges = True
End If
Next
End If
If mbNoChanges Then
For iList = 0 To .ListCount - 1
.Selected(iList) = (.List(iList) = "c")
Next
End If
End With
Application.EnableEvents = True
End Sub

Private Sub UserForm_Initialize()
With Me.ListBox1
.List = Array("a", "b", "c", "d")
End With
End Sub