Userform 3 values met to show listbox results

[ipbr21054]

A few times we’ve been searching through our database to see if a job had been previously done but spending a lot of time doing so.
Thought maybe this would be the answer to our problems. What do you think.

A user form would have say 3 combo boxes & a listbox.

Each combobox would search a specific column, example first column A second column C & third column G

The user would make a selection from each combobox & press a command button.
If all 3 selected are found then show row in listbox.

Example.
Ford, Fiesta, Add new key.

If all exist show row number otherwise msg box saying not found etc.

Currently we’ve been looking using another search option for fiesta to then find it wasnt a Add new key job so looked again & again until it was.

 

[DanteAmor]

A few times we’ve been searching through our database to see if a job had been previously done but spending a lot of time doing so.
Thought maybe this would be the answer to our problems. What do you think.

A user form would have say 3 combo boxes & a listbox.

Each combobox would search a specific column, example first column A second column C & third column G

The user would make a selection from each combobox & press a command button.
If all 3 selected are found then show row in listbox.

Example.
Ford, Fiesta, Add new key.

If all exist show row number otherwise msg box saying not found etc.

Currently we’ve been looking using another search option for fiesta to then find it wasnt a Add new key job so looked again & again until it was.

You should be more explicit in your requests.
How many columns does the list box have?
Which columns are you going to load into the list box?
Sheet name?
etc., etc., etc.

If you can't adapt the code to your needs, then you should explain in detail what you have and what you expect as a result.

Include images and sample minisheets; that would help me provide you with a suitable solution and not have you guessing all the time.

 

[ipbr21054]

@DanteAmor
Morning so a few notes ive come across whilst trying to use the code above.

I have added this code to a button to clear the listbox & Combobox.
However i see a RTE9 Subscript out of range.
When i debug i see the following line in yellow

Private Sub ResetForm_Click()
ListBox1.Clear      ** this clears listbox if i comment out the 3 combobox lines below ***
ComboBox1.Clear
ComboBox2.Clear
ComboBox3.Clear
End Sub

Sub FilterData()
  Dim cmb1 As Variant, cmb2 As Variant, cmb3 As String
  Dim i As Long, j As Long, k As Long, n As Long
  Dim b As Variant
 
  ListBox1.Clear
  n = WorksheetFunction.CountIfs(Range("D:D"), ComboBox1.Value & "*", _
        Range("F:F"), ComboBox2.Value & "*", Range("G:G"), ComboBox3.Value & "*")
  ReDim b(1 To n, 1 To 7)
 
  For i = 1 To UBound(a, 1)
    If ComboBox1.Value = "" Then cmb1 = a(i, 4) Else cmb1 = ComboBox1.Value
    If ComboBox2.Value = "" Then cmb2 = a(i, 6) Else cmb2 = ComboBox2.Value
    If ComboBox3.Value = "" Then cmb3 = a(i, 7) Else cmb3 = ComboBox3.Value
       
    If a(i, 4) = cmb1 And a(i, 6) = cmb2 And a(i, 7) = cmb3 Then
      k = k + 1
      For j = 1 To UBound(a, 2)
        b(k, j) = a(i, j)      *** this line is shown when i debug the RTE9 ***
      Next
    End If
  Next
  ListBox1.List = b
End Sub

 

[DanteAmor]

Test the code before making any changes.

Don't delete the combos, because you'll delete all the items that were loaded.

If you want to clear the combo, use combobox1.value = ""

Test my code without making any changes.

 

[ipbr21054]

I have removed the code to clear combobox & listbox in order to test the code supplied.

A few things to mention / address if possible.

My worksheet values are added as jobs are completed & then column A which is the customers name is sorted A- Z for the user to find / locate customer easier.
Because of this when i click the drop down in the combobox the values are in no order from the sheet its been taken from, i understand why but im now looking up / down for a value to select.

The code i added to a command button was to clear the combobox & listbox once finished so the user could make another search.
Currently having to close the form then open again due to the RTE

Some results that enter the listbox are in need of an adjustment to column width.


This also gave me the same RTE and line in the code yelow wehn debugged

combobox1.value = ""

 

[DanteAmor]

I can't help you any further.
You're not cooperating.
You're modifying the macro.
You're not providing the relevant information I requested.
You should start by providing sample data and images to explain what you need.
My code works with the following:

DANTE AMOR
	A	B	C	D	E	F	G
1	A	B	C	D	E	F	G
2	A2	B2	C2	A2	E2	C2	G2
3	A2	B3	C2	A2	E3	C2	G2
4	A2	B4	C2	A2	E4	C2	G2
5	A2	B5	C2	A2	E5	C2	G5
6	A2	B6	C2	A2	E6	C2	G5
7	A2	B7	C7	A2	E7	C7	G5
8	A2	B8	C7	A2	E8	C7	G5
9	A2	B9	C7	A2	E9	C7	G9
10	A2	B10	C7	A2	E10	C7	G10
11	A11	B11	C11	A11	E11	C11	G11
12	A11	B12	C11	A11	E12	C11	G11
13	A11	B13	C11	A11	E13	C11	G13
14	A11	B14	C14	A11	E14	C14	G14
15	A11	B15	C15	A11	E15	C15	G15
Hoja2

If you put my complete code in your userform with three combo boxes and a list box, you'll see that it works with the data above.
There's no need for a button; when you delete data in combos, the filter is done automatically.

 

[ipbr21054]

I have used your code & items as mentioned.

I select from first combobox, the list is updated.
I then make a selection from second combobox, the list is updated.
I then make a selection from the third combobox, the list is updated.

Now i wish to make a new search hence why i wanted to clear the combobox but as you say use your code only i then make a selection from the first combobox & because selections are shown in combobox 2 & 3 i get the RTE like advis3ed.

This is why i mentioned about clearing the comboboxes without closing / opening the form

 

[DanteAmor]

Now i wish to make a new search hence why i wanted to clear the combobox but as you say use your code only i then make a selection from the first combobox & because selections are shown in combobox 2 & 3 i get the RTE like advis3ed.

Try:

Dim a As Variant

Sub FilterData()
  Dim cmb1 As Variant, cmb2 As Variant, cmb3 As String
  Dim i As Long, j As Long, k As Long, n As Long
  Dim b As Variant
  
  ListBox1.Clear
  n = WorksheetFunction.CountIfs(Range("D:D"), ComboBox1.Value & "*", _
        Range("F:F"), ComboBox2.Value & "*", Range("G:G"), ComboBox3.Value & "*")
  If n = 0 Then
    MsgBox "There are no values to display"
    Exit Sub
  End If
  ReDim b(1 To n, 1 To 7)
  
  For i = 1 To UBound(a, 1)
    If ComboBox1.Value = "" Then cmb1 = a(i, 4) Else cmb1 = ComboBox1.Value
    If ComboBox2.Value = "" Then cmb2 = a(i, 6) Else cmb2 = ComboBox2.Value
    If ComboBox3.Value = "" Then cmb3 = a(i, 7) Else cmb3 = ComboBox3.Value
        
    If a(i, 4) = cmb1 And a(i, 6) = cmb2 And a(i, 7) = cmb3 Then
      k = k + 1
      For j = 1 To UBound(a, 2)
        b(k, j) = a(i, j)
      Next
    End If
  Next
  ListBox1.List = b
End Sub

Private Sub ComboBox1_Change()
  ComboBox2.Value = ""
  ComboBox3.Value = ""
  Call FilterData
End Sub
Private Sub ComboBox2_Change()
  ComboBox3.Value = ""
  Call FilterData
End Sub
Private Sub ComboBox3_Change()
  Call FilterData
End Sub

Private Sub UserForm_Activate()
  Dim dic1 As Object, dic2 As Object, dic3 As Object
  Dim i As Long
  Set dic1 = CreateObject("Scripting.Dictionary")
  Set dic2 = CreateObject("Scripting.Dictionary")
  Set dic3 = CreateObject("Scripting.Dictionary")
  
  a = Range("A1:G" & Range("D" & Rows.Count).End(3).Row).Value
  ListBox1.ColumnCount = 7
  
'first column D second column F & third column G
  For i = 2 To UBound(a, 1)
    dic1(Range("D" & i).Value) = Empty
    dic2(Range("F" & i).Value) = Empty
    dic3(Range("G" & i).Value) = Empty
  Next
  
  ComboBox1.List = dic1.keys
  ComboBox2.List = dic2.keys
  ComboBox3.List = dic3.keys
End Sub

 

[ipbr21054]

This will be a job for tomorrow.

Do you have any advice on how to make column widths adjustable as some values are cut off at the end & not the full value is shown.

Also the order of values in combobox as currently there is no order & just random. This then is a task having to look up & down for the value you want to select

 

[DanteAmor]

Do you have any advice on how to make column widths adjustable as some values are cut off at the end & not the full value is shown.

Also the order of values in combobox as currently there is no order & just random. This then is a task having to look up & down for the value you want to select

These topics are not relevant to your original question.
Create a new thread.