Using comboboxes to filter a listbox

[Waimea]

I have an userform with 6 comboboxes, named combobox1, combobox2, combobox3, combobox4, combobox5, combobox6 and a listbox named listbox1.

I have a textbox named textbox1 and a button named commandbutton1.

I want to filter the listbox with criteria in the comboboxes such as "> 200" or "< 1000."

So if I select ">200" in combobox1 and ">5000" in combobox2 the items where this is true would populate the listbox.

Private Sub CommandButton1_Click()
    Dim Crit1 As String
    Dim Crit2 As String
    Dim Crit3 As String
    Dim Crit4 As String
    Dim Crit5 As String
    Dim Crit6 As String
    
    Crit1 = ComboBox1.Value
    Crit2 = ComboBox2.Value
    Crit3 = ComboBox3.Value
    Crit4 = ComboBox4.Value
    Crit5 = ComboBox5.Value
    Crit6 = ComboBox6.Value

End Sub

I have searched and found this thread https://www.mrexcel.com/forum/excel...utofilter-2.html?highlight=listbox+search+vba However the code doesn't do what I am trying to do.

Do I need to use autofilter? Where do I start?

 

[Waimea]

I came up with another question, I am using the following code in a second userform:

Private Sub UserForm_Initialize()

With UserForm1.ListBox1
    ' Exit sub if no item is selected in listbox1
    If .ListIndex = -1 Then Exit Sub
 
    ComboBox1.Value = .List(.ListIndex, 0)
    ComboBox2.Value = .List(.ListIndex, 1)
    
    TextBox1.Text = .List(.ListIndex, 2)
    
End With

I am using the items in the array to populate the second userform controls.

How would I save if I change one entry? Do I write to the array and then dump the array on another ws?

 

[MickG]

You need to explain what your trying to do , that code just places single selected values from listbox1 to comboboxes/textboxes on another userform.

 

[Waimea]

Hi Mick,

thank you again for your help!

I am loading a table into a userform as an array, I am then loading the array into a listbox.

Say I found a match with all 6 criteria in the userform you helped me with,

I then double click on that match and a second userform is opened.

I load the values of the listbox into other userforms controls like comboboxes or textboxes.

How would I edit an entry? And how would I save the modified entry? And I am saving the array or the table contents?

 

[MickG]

Why don't you load the listbox selections straight onto a sheet, instead of another Userform.

 

[Waimea]

Hi Mick,

I am not sure why, I am trying to use Userforms as much as possible. What should I do with the data when it is on the sheet?

I figured out how to edit but I don't get how to pass the selected value from userform1.listbox1 to userform2.listbox1?

So if I selected Asia in the userform you helped me with, I would get all entries for Asia in listbox2?

 

[MickG]

What do you intend to do with it when you have it on Userform2 and listbox2 ???

 

[Waimea]

Hi Mick,


I am trying to narrow my search and your code is great and gives me a way to find items with many criteria.

My userform2 shows an edit entry/item and I want to show all similar items to avoid duplicates.

 

[MickG]

I have to go back to my Question ??
Please describe how you intend to fill listbox2 and what you intend to do with this new data.

For example you may intend to fill userform2 with multiple lines based on multiple criteria from Userform 1. One you have this data on Userform2, what do you intend to do with it ????

 

[Waimea]

Hi Mick,

sorry for not answering your question! I want to fill combobox 2 with no criteria from userform1.listbox1.

I want to show multiple lines in userform2.listbox1 that are the same as continent and/or country in userform1.listbox1.

I am using an userform for the ability to add new information and I want to be able to see all similar items so that I can minimize the number of duplicates. I intend o add/edit/delete items from userform2.

Ex. I select Asia in combobox1 and listbox1 is filtered to show all items with Asia, I then select more then >5000 and I filter to fewer alternatives.

I then double click on an item and userform2 opens with all items for asia in listbox1.

I hope this answers your question?

 

[MickG]

Are you saying that whatever the results are from the filter on Userform 1/Listbox, that when you then click any item on those results, then Userform2 is opened and those items are copied to Userform2/Listbox1.

What happens then.???