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