Multi-Criteria Search Box for dynamic listbox

[bmu13]

I am using 2 textboxes, 1 listbox, 1 button. I simply want to show a list that contain what is entered in the textboxes.

textbox1 = color
textbox2 = shape

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]TEXTBOX 1[/TD]
[TD="align: center"]TEXTBOX 2[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]BLUE-CIRCLE[/TD]
[TD="align: center"]BLUE[/TD]
[TD="align: center"]CIRCLE[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]GREEN-TRIANGLE[/TD]
[TD="align: center"]GREEN[/TD]
[TD="align: center"]TRIANGLE[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]RED-TRIANGLE[/TD]
[TD="align: center"]RED[/TD]
[TD="align: center"]TRIANGLE[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]RED-SQUARE[/TD]
[TD="align: center"]RED[/TD]
[TD="align: center"]SQUARE[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]BLUE-SQUARE[/TD]
[TD="align: center"]BLUE[/TD]
[TD="align: center"]SQUARE[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]RED-TRIANGLE[/TD]
[TD="align: center"]RED[/TD]
[TD="align: center"]TRIANGLE[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]GREEN-CIRCLE[/TD]
[TD="align: center"]GREEN[/TD]
[TD="align: center"]CIRCLE[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]GREEN-SQUARE[/TD]
[TD="align: center"]GREEN[/TD]
[TD="align: center"]SQUARE[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]GREEN-SQUARE[/TD]
[TD="align: center"]GREEN[/TD]
[TD="align: center"]SQUARE[/TD]
[/TR]
</tbody>[/TABLE]

What I have:
What works:

If textbox1.value <> "" and textbox2.value = "" then
set rng = .range("B1:B9")
set fnd = rng.Find(textbox1.value)
Listbox1.clear
if fnd is nothing then ListBox1.AddItem "Result Not Found": Exit Sub
Set first = fnd
With Listbox1
Listbox1.clear
Listbox1.AddItem fnd.Offset(, -1)
Do
Set fnd = rng.FindNext(fnd)
If fnd.Address = First.Address then Exit Do
ListBox1.AddItem fnd.Offset(,-1)
Loop
End With

What doesn't work

Elseif textbox1.value <> "" and textbox2.value <> "" then
set rng.Range("A1:C9")
ctr1 = 1
For i = 1 To 9
If StrComp(textbox1.value, rng(i, 2)) <> 0 then
arr1(ctr1) = i
ctr1 = ctr1 + 1
End If
Next i
ctr2 = 1
For j = 1 To Len(arr1)
If StrComp(textbox2.value, rng(arr1(j), 3)) = 0 then
arr2(ctr2) = arr1(j)
output(ctr2, 1) = rng(arr1(j), 3)
ctr2 = ctr2 + 1
End if
Next j
Listbox1.clear
Listbox1.List = output
End if


What I want:

textbox1.value = red textbox1.value = red
textbox2.value = "" textbox2.value = triangle


[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]RED-TRIANGLE[/TD]
[TD="align: center"]RED[/TD]
[TD="align: center"]TRIANGLE[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]RED-SQUARE[/TD]
[TD="align: center"]RED[/TD]
[TD="align: center"]SQUARE[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]RED-TRIANGLE[/TD]
[TD="align: center"]RED[/TD]
[TD="align: center"]TRIANGLE[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="class: grid, width: 500, align: right"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]RED-TRIANGLE[/TD]
[TD="align: center"]RED[/TD]
[TD="align: center"]TRIANGLE[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]RED-TRIANGLE[/TD]
[TD="align: center"]RED[/TD]
[TD="align: center"]TRIANGLE[/TD]
[/TR]
</tbody>[/TABLE]



Elseif




[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD="align: center"]Listbox1[/TD]
[/TR]
[TR]
[TD="align: center"]Red-Triangle
Red-Square
Red-Triangle[/TD]
[/TR]
</tbody>[/TABLE]

Elseif[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD="align: center"]Listbox1[/TD]
[/TR]
[TR]
[TD="align: center"]Red-Triangle
Red-Triangle[/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG15Aug09
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, Ray(), c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B1"), Range("B" & Rows.Count).End(xlUp))
ListBox1.Clear
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        [COLOR="Navy"]If[/COLOR] UCase$(Dn.Value) = UCase$(TextBox1.Value) And UCase$(TextBox2.Value) = UCase$(Dn.Offset(, 1).Value) [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            ReDim Preserve Ray(c)
            Ray(c) = Dn.Offset(, -1)
        [COLOR="Navy"]ElseIf[/COLOR] UCase$(Dn.Value) = UCase$(TextBox1.Value) And UCase$(TextBox2.Value) = "" [COLOR="Navy"]Then[/COLOR]
            c = c + 1
            ReDim Preserve Ray(c)
           Ray(c) = Dn.Offset(, -1)
        [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] Dn
ListBox1.List = Application.Transpose(Ray)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[bmu13]

Mick, thank you! Your code worked perfectly. Now, the only thing missing is the scenario in which the user inputs a value that doesn't exist within the range. For example, if textbox1 = pink or textbox2 = cube. then the listbox should say "Result Not Found". How would you make this slight modification?

 

[MickG]

Try Changing the End of the code as shown in Red:-

Next Dn
[B][COLOR=#FF0000]If c > 0 Then
    ListBox1.List = Application.Transpose(Ray)
Else
     MsgBox "No Data Found!!"
End If[/COLOR][/B]
End Sub