Removing the row highlight from selected row in a list box-CodePudding

Working on my first Excel VBA project so very new. The application keeps track of inventory, orders, and legal information that has to be associated with an order. The application has multiple user forms that handle various state changes as inventory is added and orders are processed. In particular there is a different user form for each thing the user must do to move an order along.

I have some code that dynamically searches through a worksheet to find orders in a worksheet that are in a particular state. I populate the search results in a different spreadsheet.

The user can then select an order and perform the steps to advance it along the process. I display the search results to the user using the RowSource property.

After the user has made a selection and processed the order, I want to return to the user form to process other orders in the same state. So, I clear the form and redo search and display the results. But when I do this the original selected row in the list box is still highlighted.

I want to clear this highlight when I repopulate the list box. I am clearing the list box and repopulating it but the highlight on the selected row remains and I cannot seem to remove it.

Here is what it looks like:

This is a picture of the list box after clearing it and repopulating the data in it

I have tried several things and here is my current code but none of this works

Private Sub ClearListBox()
    
    Dim varItm As Variant

'    Me.lstOpenO.Clear
'    Me.lstOpenO.ListIndex = 0
    Me.lstOpenO.Value = ""
    Me.lstOpenO.ListIndex = -1
    Me.lstOpenO.RowSource = ""
    With lstOpenO
        varItm = .MultiSelect
        .MultiSelect = 0
        .MultiSelect = 1
        .MultiSelect = varItm
    End With
 
End Sub

I saw in another post that you can unload a user form. I tried putting this code in the ClearList subroutine (see below). When I do this each time, I execute the load command it executes the Initialize Userform5 subroutine. So, it gets into an infinite loop. I tried declaring a public Boolean variable to prevent this but every time I load the User Form the variable gets reset. Is there some way to make this work.

Private Sub ClearListBox()

    Unload UserForm5
    Load UserForm5

End Sub

Thanks for any help you can give.

CodePudding user response：

If it's a Multiselect listbox, you need to loop through the Selected item array.

Dim x As Integer
For x = 0 To lstOpenO.ListCount - 1
  lstOpenO.Selected(x) = False
Next

CodePudding user response：

*braX Thanks! *

My list box is a single selection but based on your answer I tried this. This list box is being filled from a worksheet called "Search" so first I cleared this and filled the list box with only the headers. The I changed my list to be multiselect and used your code to clear the selection. The end result is the highlight is cleared as are the contents of the list.

' Clear search worksheet
NRow = [Counta(Search!A:A)] ' identify last row
For iRow = 2 To NRow
    For Column = 1 To 12
        Sheets("Search").Cells(iRow, Column) = ""
    Next
Next

'Clear list box
Me.lstOpenO.RowSource = "Search!A2:L2"
Me.lstOpenO.MultiSelect = fmMultiSelectMulti
For x = 0 To lstOpenO.ListCount - 1
    lstOpenO.Selected(x) = False
Next
Me.lstOpenO.MultiSelect = fmMultiSelectSingle