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]

There's some missing data, which I'll have to assume.
- The sheet name.
- That you've already loaded the data into the combo boxes.
- That you want to load 7 columns into the list box, from columns A to G.

Try this:
Notes:
1. The command button is not required.
2. With this technique, every time you select a data from any of the comboboxes, the filter is automatically performed and the listbox is filled.
3. The global variable 'a' is required at the beginning of the entire code.
4. The variable 'a' must be filled in the Activate event.

Option Explicit

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("A:A"), ComboBox1.Value & "*", _
        Range("C:C"), 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, 1) Else cmb1 = ComboBox1.Value
    If ComboBox2.Value = "" Then cmb2 = a(i, 3) Else cmb2 = ComboBox2.Value
    If ComboBox3.Value = "" Then cmb3 = a(i, 7) Else cmb3 = ComboBox3.Value
        
    If a(i, 1) = cmb1 And a(i, 3) = 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()
  Call FilterData
End Sub
Private Sub ComboBox2_Change()
  Call FilterData
End Sub
Private Sub ComboBox3_Change()
  Call FilterData
End Sub

Private Sub UserForm_Activate()
  a = Range("A1:G" & Range("A" & Rows.Count).End(3).Row).Value
  ListBox1.ColumnCount = 7
End Sub

 

[ipbr21054]

Thanks,
Currently been advised to put this on hold at present.
I will save the code.

Many thanks

 

[DanteAmor]

You could at least try it and comment if it solves your request.

 

[ipbr21054]

I have that on my list of things to do once I’ve sorted how to load data into comboboxes.

 

[DanteAmor]

You can simulate the loading of combo boxes with 2 or 3 examples using additem. So you can test my model. And in another thread, you ask for support to help you load combo boxes. There, you explain in detail and with examples how you want the combo boxes to load.

 

[DanteAmor]

I've provided some code to load the combo boxes. I assume the data is in the same sheet.
The combo boxes will be loaded as an example so you can test the functionality you requested in this thread.

Option Explicit

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("A:A"), ComboBox1.Value & "*", _
        Range("C:C"), 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, 1) Else cmb1 = ComboBox1.Value
    If ComboBox2.Value = "" Then cmb2 = a(i, 3) Else cmb2 = ComboBox2.Value
    If ComboBox3.Value = "" Then cmb3 = a(i, 7) Else cmb3 = ComboBox3.Value
        
    If a(i, 1) = cmb1 And a(i, 3) = 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()
  Call FilterData
End Sub
Private Sub ComboBox2_Change()
  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("A" & Rows.Count).End(3).Row).Value
  ListBox1.ColumnCount = 7
  
'first column A second column C & third column G
  For i = 2 To UBound(a, 1)
    dic1(Range("A" & i).Value) = Empty
    dic2(Range("C" & i).Value) = Empty
    dic3(Range("G" & i).Value) = Empty
  Next
  
  ComboBox1.List = dic1.keys
  ComboBox2.List = dic2.keys
  ComboBox3.List = dic3.keys
End Sub

You shouldn't ask for help and then dismiss it without even trying.

I like helping people, but it's not nice of you to dismiss my work.
I understand that setbacks arise, but there you have the code. It's one thing to request time to review it, and another to store it in the vault in case you need it later.

 

[ipbr21054]

Morning,
I thankyou for your advice but i didnt dismiss it as i had to put it on hold a while as something else took priority.
The management is out this afternoon so i will be able to take a look then.

I will advise later my findings.
Thanks

 

[ipbr21054]

@DanteAmor So ive now been told to use this code so its now installed.

Powers to be from above have said we need to change columns.

Originally i was informed to use columns A C & G but now we are to use D F & G

So in the code supplied by @DanteAmor i can see that i need to change the vales mentioned below BUT where else as making a selection from the Combobox i ret a RTE 9 subscript out of range

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("A:A"), ComboBox1.Value & "*", _ *** CHANGE THIS VALUE A:A TO D:D ***
        Range("C:C"), ComboBox2.Value & "*", Range("G:G"), ComboBox3.Value & "*")  *** CHANGE THIS VALUE C:C TO F:F ***
  ReDim b(1 To n, 1 To 7)
  
  For i = 1 To UBound(a, 1)
    If ComboBox1.Value = "" Then cmb1 = a(i, 1) Else cmb1 = ComboBox1.Value *** CHANGE VALUE (i,1) TO (i,4) ***
    If ComboBox2.Value = "" Then cmb2 = a(i, 3) Else cmb2 = ComboBox2.Value *** CHANGE VALUE (i,3) TO (i,6) ***
    If ComboBox3.Value = "" Then cmb3 = a(i, 7) Else cmb3 = ComboBox3.Value
        
    If a(i, 1) = cmb1 And a(i, 3) = 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()
  Call FilterData
End Sub

Private Sub ComboBox2_Change()
  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("A" & Rows.Count).End(3).Row).Value *** CHANGE A1:G TO D1:G ALSO THE SEPARATE A TO D ***
  ListBox1.ColumnCount = 7
  
'first column A second column C & third column G
  For i = 2 To UBound(a, 1)
    dic1(Range("A" & i).Value) = Empty *** CHANGE THIS FROM A TO D ***
    dic2(Range("C" & i).Value) = Empty *** CHANGE THIS FROM C TO F ***
    dic3(Range("G" & i).Value) = Empty
  Next
  
  ComboBox1.List = dic1.Keys
  ComboBox2.List = dic2.Keys
  ComboBox3.List = dic3.Keys
End Sub

 

[DanteAmor]

I'm sending you the updated code.
Matrix 'a' will be loaded from column A, even if you don't use columns A, B, and C.

Option Explicit

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 & "*")
  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()
  Call FilterData
End Sub
Private Sub ComboBox2_Change()
  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

Before making changes to the code, test with generic data.