Userform Listbox Search Function

[cpounds217]

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

[SamT]

search function in a multiselect Listbox, but everytime I initiate the search process (through the textbox_change)

So... Which is it? A ListBox or a TextBox?

added to the string behind the multi-select string creation function.

What is the purpose of SelectedEHSList.

Obviously we don't understand what you are trying to accomplish with all this. It's much better if you tell us what you are wanting to accomplish, rather than how you are trying to do it. Obviously, the method you are using to do it isn't working, or you wouldn't be here.

Describe the State the UserForm is in before the [ListBox|Textbox] change and the State you want it to wind up in.

[cpounds217]

So... Which is it? A ListBox or a TextBox?

What is the purpose of SelectedEHSList.

Obviously we don't understand what you are trying to accomplish with all this. It's much better if you tell us what you are wanting to accomplish, rather than how you are trying to do it. Obviously, the method you are using to do it isn't working, or you wouldn't be here.

Describe the State the UserForm is in before the
[ListBox|Textbox] change and the State you want it to wind up in.

I have a ListBox which is around 500 items long. I have a textbox next to it that is meant to search the listbox. The problem is that the listbox.selected clears when the search function is used.

Example:
(Everything is blue is "selected")
List 1
a
b
c
d
e
f
g
h

So now I want to search something that is "ABCD" and is in the middle of all 500 items. I do so and the items that are blue above become unselected.

Thanks

[SamT]

a textbox next to it that is meant to search the listbox

Unfortunately, that is a nonsense statement, but I get what you want... which is to extract the selected items from the listbox and place a concatenated string of the selected items into the TextBox. You also want the selected items viewable by the User in the listbox.


Why not... Place the selected items into an array, concatenate that array into the Textbox, THEN, set that same array as the ListBox.List. Upon User confirmation or rejection, replace the ListBox.List with the original List. An issue is that the new list is only the previously selected items


If that won't work for you, try using the Before Exit event

Private Sub ListboxName_Exit( ByVal Cancel  As MSForms.ReturnBoolean)
Cancel = True
Call the Search and Concatenate sub
End Sub

Personally, I would see if I could break the 500 item list into categories and use two listboxes, one dependent to the other.

Note that I still don't understand what you mean by "Search." Search for what? Where? Why?????

Let me put my question this way... The User selects a bunch of Items, then a concatenated string of the selections are placed in a TextBox. Why do you need anything more than that?

[cpounds217]

Unfortunately, that is a nonsense statement, but I get what you want... which is to extract the selected items from the listbox and place a concatenated string of the selected items into the TextBox. You also want the selected items viewable by the User in the listbox.


Why not... Place the selected items into an array, concatenate that array into the Textbox, THEN, set that same array as the ListBox.List. Upon User confirmation or rejection, replace the ListBox.List with the original List. An issue is that the new list is only the previously selected items


If that won't work for you, try using the Before Exit event

Private Sub ListboxName_Exit( ByVal Cancel  As MSForms.ReturnBoolean)
Cancel = True
Call the Search and Concatenate sub
End Sub

Personally, I would see if I could break the 500 item list into categories and use two listboxes, one dependent to the other.

Note that I still don't understand what you mean by "Search." Search for what? Where? Why?????

The textbox is meant as a SEARCH FUNCTION. It’s job is to “search” within the Listbox to help find the next item to be selected.

I cannot narrow them down as the list is a specific list from the EPA. There aren’t multiple categories. I provided the spreadsheet, why don’t you take a look if you are so confused. Usually i refused to post the spreadsheet and i get yelled at, now I provide it, and so far you have decided to not use it.

[SamT]

You just want the selected items to remain selected. If using the BeforeExit event doesn't work, I'm lost. You'll just have to note which Items are selected and reselect them.

[cpounds217]

You just want the selected items to remain selected. If using the BeforeExit event doesn't work, I'm lost. You'll just have to note which Items are selected and reselect them.

What I want to do is the following:

I have a ListBox, which is filled with a list of around 350-500 items. I also have a textbox and commandbutton, which their combined function is narrow-down the results in the listbox (SearchFunction), based on what is in the textbox.

The problem is that whenever the SearchFunction is used, the selected items in the listbox are deselected.

What I want is for the SearchFunction to work as it has been, but not deselect the items. The result will be something like:

Before search: str = X, Y, Z
Searching for: D
After Search: str = D, X, Y, Z

Just to reiterate, the current function would have the example above resulting in After Search: str = D.

[SamT]

What I want is ..., but not deselect the items.

Reselect items with

For i = LBound(EHSList.List) To UBound(EHSList.List)
  If InStr(SelectedEHSList, EHSList(i)) > 0 Then EHSList(i).Selected = True
Next

[cpounds217]

Reselect items with

For i = LBound(EHSList.List) To UBound(EHSList.List)
  If InStr(SelectedEHSList, EHSList(i)) > 0 Then EHSList(i).Selected = True
Next

Based on what you suggested, and my attempted test, you were aiming at the creation of the string. I am looking for the search part.

[SamT]

Originally Posted by SamT

Reselect items with

     For i = LBound(EHSList.List) To UBound(EHSList.List)
  If InStr(SelectedEHSList, EHSList(i)) > 0 Then EHSList(i).Selected = True
Next

Originally Posted by cpounds217
Based on what you suggested, and my attempted test, you were aiming at the creation of the string.

Really?