Hello Everyone!
I have been trying to use a search function in a multiselect Listbox, but everytime I initiate the search process (through the textbox_change) anything selected in the listbox is unselected. This of course defeats the purpose of the search function. My prior method was to search the listbox, and then have the user insert the selected item into a textbox, but this was tedious and included unnecessary steps for the user. I would rather just search, select, and be added to the string behind the multi-select string creation function.
The list behind the listbox, for a point of reference as to why the search is needed, is about 500 items long.
I have attached the spreadsheet, and the code. The first Code is the actual search function, while the second is the function to create the string from the multi-select listbox.
Any help you can provide will be greatly appreciated.
Thank you!
Tier II Original - VBA Code Copy - Ver 2.1.xlsm
Private Sub SearchChemical_Change()
Dim MyList() As Variant
Dim X As Long
Dim Y As Long
Dim FoundSomething As Boolean
FoundSomething = False
Y = 0
For X = 1 To wsLists.Range("J" & Rows.Count).End(xlUp).Row
If InStr(1, UCase(wsLists.Range("J" & X).Value), UCase(SearchChemical)) > 0 Then
FoundSomething = True
ReDim Preserve MyList(Y)
MyList(Y) = wsLists.Range("J" & X).Text
Y = Y + 1
End If
Next
If FoundSomething Then
EHSList.List = MyList
Else
EHSList.Clear
End If
End Sub
Private Function EHS_Selection() As String
Dim i As Long
Dim SelectedEHSList As String
For i = LBound(EHSList.List) To UBound(EHSList.List)
If EHSList.Selected(i) Then
SelectedEHSList = SelectedEHSList & EHSList.List(i) & ", "
End If
Next
If SelectedEHSList <> "" Then
SelectedEHSList = Left(SelectedEHSList, Len(SelectedEHSList) - 2)
EHS_Selection = SelectedEHSList
ElseIf ePlanCheckbox.Value = True Then
EHS_Selection = "See E-Plan"
ElseIf SubjectComboBox = "Tier II" Then
EHS_Selection = "No EHS"
End If
End Function