Clearing a list box and any selected items in list

Question

I have an Excel VBA application that goes through a sheet which contains product orders on a sheet in a workbook and searches the worksheet for orders that match various criteria which is populates in a search worksheet. The contents from this worksheet are then displayed in list box. There are several user forms that allow the user to select an order and then manipulate the order. After doing this the order manipulated may not meet the search criteria so I want to clear the list box contents and the selected row in the list box. I have tried numerous things but nothing seems to work. My latest is something list this:

Private Sub ClearListBox()
    UserForm5.lstOpenO.ListIndex = -1
    UserForm5.lstOpenO.RowSource = ""
End Sub

But I have tried setting the UserForm5.lstOpenO.Selected to false for all the rows. I have tried clearing the search worksheet and then displaying that which should only show the headers on the columns but the highlight in the selected row remains.

Any help would be greatly appreciated

Bruce

Answer 1

First of all you should not use the default instance of the userform in your code. This will lead to ambigous behaviour

I'd suggest

Private Sub ClearSelectionInListBox()
Dim varItm as variant
   With lstOpen0
        varItm = .MultiSelect
        .MultiSelect = 0
        .MultiSelect = 1
        .MultiSelect = varItm
   End With
End Sub

This will clear the selection within the listbox.

It is not clear if you really want to clear the contents. Because if you want then it does not make sense to think about clearing the selection.

If you want to clear the listbox then it is not neccessary to clear the selection first.

Private ClearListBox()
    With lstOpen0
        .RowSource = ""
        .Clear
    End With
End Sub

But after that you need to fill the listbox again.

Further reading

VBA userform

Userform.Show

ListBox