Filter Function in User Form

[charly1]

Hi
In my spreadsheet I have a table and have used the filter with the choose column function to filter data based on a name i insert into an adjacent cell.
The question is can this be done in a similar manner within a user form. I already have in my userform a combo box that displays all data in Table1 column x. Is there a way to add a command button to filter all matching cells in table and return all results to populate a list box.
Thanks ever so much for any help.

 

[Jeffrey Mahoney]

Yes. I created a generic table and userform as an example. You'll have to replace the sheet names, ranges, form control names, and such

Private Sub TextBox1_Change()
  Dim Rng As Range
  Dim Ary As Variant
  Dim App As Application
  Dim WF As WorksheetFunction
  Dim TBVal As String
  Dim Sht As Worksheet
  Dim Crit As String
  Dim Cel As Range
  Dim C1 As String
  
  C1 = Chr(1)
  Set Sht = Sheets("Sheet1")
  TBVal = UCase(TextBox1.Value)
  Set Rng = Sht.Range("Table1[Two]")
  If TBVal = "" Then
    Ary = Rng.Value
    ListBox1.List = Ary
  Else
    For Each Cel In Rng
      If InStr(UCase(Cel.Value), TBVal) > 0 Then
        If Len(Crit) > 0 Then
          Crit = Crit & C1 & Cel.Value
        Else
          Crit = Cel.Value
        End If
      End If
    Next Cel
    If Crit <> "" Then
      Ary = Split(Crit, C1)
      ListBox1.List = Ary
    End If
  End If
  

End Sub



Private Sub UserForm_Initialize()
  Dim Rng As Range
  Dim Ary As Variant
  Dim App As Application
  Dim WF As WorksheetFunction

  Set Rng = Sheets("Sheet1").Range("Table1[Two]")
  Ary = Rng.Value
  ListBox1.List = Ary
  
End Sub

Book1
	A	B	C
1	One	Two	Three
2	1	Monday1	Monday
3	2	Tuesday2	Tuesday
4	3	Wednesday3	Wednesday
5	4	Thursday4	Thursday
6	5	Friday5	Friday
7	6	Saturday6	Saturday
8	7	Sunday7	Sunday
9	8	Monday8	Monday
10	9	Tuesday9	Tuesday
11	10	Wednesday10	Wednesday
12	11	Thursday11	Thursday
13	12	Friday12	Friday
14	13	Saturday13	Saturday
15	14	Sunday14	Sunday
16	15	Monday15	Monday
17	16	Tuesday16	Tuesday
18	17	Wednesday17	Wednesday
19	18	Thursday18	Thursday
20	19	Friday19	Friday
Sheet1

Cell Formulas
Range	Formula
B2:B20	B2	=[@Three]&A2
C2:C20	C2	=TEXT(DAY(A2+1),"dddd")

 

[Jeffrey Mahoney]

I didn't actually create a button for you. The textbox on the form allows you to filter the range by adding text you want to match. That updates the listbox each time a character is added or subtracted.

 

[charly1]

Gosh, Thanks a million, it works beautifully.

Would it to much effort to advise me on how to edit this code to include more than a single column of data from the table range and to include the results in the list box as multicolumns?

Thanks again