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!

22022


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 :dunno


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 :dunno


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]

http://www.vbaexpress.com/forum/images/misc/quote_icon.png Originally Posted by SamT http://www.vbaexpress.com/forum/images/buttons/viewpost-right.png (http://www.vbaexpress.com/forum/showthread.php?p=379169#post379169)

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

http://www.vbaexpress.com/forum/images/misc/quote_icon.png Originally Posted by cpounds217 http://www.vbaexpress.com/forum/images/buttons/viewpost-right.png (http://www.vbaexpress.com/forum/showthread.php?p=379169#post379169)
Based on what you suggested, and my attempted test, you were aiming at the creation of the string.Really?