Deselect single select listbox

Question

I have a listbox on a userform which on which at any point a maximum of 1 items may be selected. setting the listbox to MultiSelect = fmMultiSelectSingle provides this but prevents the user from deselecting the selected list item.

To accomplish this, I added a MouseDown event which fires when an item is clicked regardless of the current selection state as opposed to the Click event which only fires when a new item is clicked.

My code (simplified below) lets a user select an entry. When the entry is clicked, it is selected. When attempting to deselect the same entry by clicking it, the MouseDown is fired, the item is deselected, which fires the change event, which is cut short by the blnDisableEvents set to True and returns to the MouseDown sub which then runs to the end. But after the End Sub on the MouseDown sub, the item which was just deselected, is reselected which again fires the Change event.

How can I prevent the reselection of the just deselected item in the listbox?

Setup:
Userform with a single Listbox called ListBox1

Code:

Option Explicit
Dim blnDisableEvents As Boolean

Private Sub ListBox1_Change()
    If blnDisableEvents Then Exit Sub
End Sub

Private Sub ListBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If Not Me.ListBox1.ListIndex = -1 Then
        blnDisableEvents = True
        Me.ListBox1.Selected(Me.ListBox1.ListIndex) = False
        blnDisableEvents = False
    End If

End Sub

Private Sub userform_initialize()
    Dim i As Integer
    With Me
        For i = 1 To 2
            .ListBox1.AddItem i
        Next
    End With

End Sub

Why not just use the DblClick event to set the Listindex to -1? — Rory
Sure that could work @Rory, but a user then needs to know to doubleclick to deselect, something I'd like to avoid if possible to keep it as simple as possible for the user. — SilentRevolution
Then I'd suggest a combobox if they should be allowed to not select anything, or simply add a "None" item at the start of the list. — Rory
Or you add a static boolean variable (eg. bolDeselectListbox), which you set to true in the mouse-down and check later in an OnChange event. — Johanness
@Rory, I like that idea. The point is, I need to have a list of shift dates and a list of employees and depending on which is selected first, the available shifts for that employee or available employees for that shift are displayed. And when the second list is used, not have it affect the first list until the first list is deselected. — SilentRevolution

Malamare Malamare · Accepted Answer · 2021-02-02T14:15:02

Create a project with a module (Module1), an UserForm (UserForm1) in which there is a ListBox (ListBox1) and two buttons (cbuOk, cbuCancel) and four cells in Sheet1 Sheets1.Range ("B1: B4") = (Item1, Item2, Item3, Item4) as shown in this image:

Then click on a cell in column A. Ok to finish and write the selected Item in the selected cell.

'===============Sheet1 Code================
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Set grngRangeSel = Target
    If Target.Count > 1 Then Exit Sub
    
    Select Case Target.Column
    Case 1
        UserForm1.Show
    End Select
End Sub

'===============Module1 Code================

Option Explicit

Public grngRangeSel As Range

'===============UserForm1 Code================

Option Explicit

Private miPrevSel  As Integer

Private Sub UserForm_Initialize()
    
    With Me.ListBox1
        .Clear
        .List = ThisWorkbook.ActiveSheet.Range("B1:B4").Value
    End With
    
    If grngRangeSel.Value <> "" Then
        Dim Item As Integer
        With Me.ListBox1
            For Item = 0 To .ListCount - 1
                If grngRangeSel.Value = .List(Item) Then
                    .Selected(Item) = True
                    miPrevSel = Item
                    Exit For
                End If
            Next Item
        End With
    End If
        
End Sub

Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    With Me.ListBox1
        If miPrevSel = -1 Then
            miPrevSel = .ListIndex
        ElseIf miPrevSel <> .ListIndex Then
            .Selected(miPrevSel) = False
            miPrevSel = .ListIndex
        ElseIf miPrevSel = .ListIndex And .Selected(miPrevSel) = False Then
            miPrevSel = -1
        ElseIf miPrevSel = .ListIndex And .Selected(miPrevSel) = True Then
            .Selected(miPrevSel) = False
            miPrevSel = -1
       End If
    End With
End Sub

Private Sub cbuOk_Click()
    Application.EnableEvents = False
    If Me.ListBox1.ListIndex <> -1 Then
        grngRangeSel.Value = Me.ListBox1.List(Me.ListBox1.ListIndex)
        Application.Goto grngRangeSel.Offset(0, 1)
    Else
        grngRangeSel.Value = ""
        Application.Goto grngRangeSel.Offset(0, 1)
    End If
    Application.EnableEvents = True
    Unload Me
End Sub

Private Sub cbuCancel_Click()
    Application.Goto grngRangeSel.Offset(0, 1)
    Unload Me
End Sub

Deselect single select listbox

1 Answers