Filter listbox

[charly1]

Hi all

In a previous inquiry I was shown how to create a user form with a dynamic filter, to populate a list box based on data entered into a separate textbox.

I need though for the list box to be multicolumn, displaying the data from multiple columns in the source table, not just the cells that match the data in the textbox.

I cant seem to get my head around the code though, and if anyone out there is able to help me, it would be greatly appreciated.
please find link of current user form attached.

Book1.xlsm

Sheet1 one,two,three 1,Monday1,Monday 2,Tuesday2,Tuesday 3,Wednesday3,Wednesday 4,Thursday4,Thursday 5,Friday5,Friday 6,Saturday6,Saturday 7,Sunday7,Sunday 8,Monday8,Monday 9,Tuesday9,Tuesday 10,Wednesday10,Wednesday 11,Thursday11,Thursday 12,Friday12,Friday 13,Saturday13,Saturday 14,Sunday14,Su...

docs.google.com

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Vba Filter function with Listbox
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[Fluff]

How about

Private Sub TextBox1_Change()
   Dim Ary As Variant, Rws As Variant
   Dim TBVal As String
   Dim Ws As Worksheet
   
   
   Set Ws = Sheets("Sheet1")
   TBVal = TextBox1.Value
   With Ws.ListObjects("Table1").DataBodyRange
      If TBVal = "" Then
         Ary = .Value
      Else
         Rws = Filter(Evaluate(Replace("transpose(if(isnumber(search(""" & TBVal & """,@)),row(@)-min(row(@))+1,""X""))", "@", .Columns(2).Address)), "X", False)
         If UBound(Rws) < 0 Then
            Me.ListBox1.List = Array("No matches")
            Exit Sub
         ElseIf UBound(Rws) = 0 Then
            Ary = .Rows(Rws(0)).Value
         Else
            Ary = Application.Index(.Value, Application.Transpose(Rws), Evaluate("column(" & .Address & ")"))
         End If
      End If
   End With
   With Me.ListBox1
      .ColumnCount = UBound(Ary, 2)
      .List = Ary
   End With
End Sub

Private Sub UserForm_Initialize()
   Dim Ary As Variant
   
   Ary = Sheets("Sheet1").ListObjects("Table1").DataBodyRange.Value
   With Me.ListBox1
      .ColumnCount = UBound(Ary, 2)
      .List = Ary
   End With
End Sub

 

[charly1]

How about

Private Sub TextBox1_Change()
   Dim Ary As Variant, Rws As Variant
   Dim TBVal As String
   Dim Ws As Worksheet
  
  
   Set Ws = Sheets("Sheet1")
   TBVal = TextBox1.Value
   With Ws.ListObjects("Table1").DataBodyRange
      If TBVal = "" Then
         Ary = .Value
      Else
         Rws = Filter(Evaluate(Replace("transpose(if(isnumber(search(""" & TBVal & """,@)),row(@)-min(row(@))+1,""X""))", "@", .Columns(2).Address)), "X", False)
         If UBound(Rws) < 0 Then
            Me.ListBox1.List = Array("No matches")
            Exit Sub
         ElseIf UBound(Rws) = 0 Then
            Ary = .Rows(Rws(0)).Value
         Else
            Ary = Application.Index(.Value, Application.Transpose(Rws), Evaluate("column(" & .Address & ")"))
         End If
      End If
   End With
   With Me.ListBox1
      .ColumnCount = UBound(Ary, 2)
      .List = Ary
   End With
End Sub

Private Sub UserForm_Initialize()
   Dim Ary As Variant
  
   Ary = Sheets("Sheet1").ListObjects("Table1").DataBodyRange.Value
   With Me.ListBox1
      .ColumnCount = UBound(Ary, 2)
      .List = Ary
   End With
End Sub

It works like a charm. Thanks a million.

If its not to much to ask, might I request an adaption?

The spreadsheet I attached in my original query was a sample, [3 col of 20 rows], My spreadsheet that i actually need this user form for is a much larger table. I assumed that I would be able to adapt an answer to suit my spreadsheet, however after trying it seems a little beyond me.
My table actually consists of 15 columns. [A-O]. Column number 14 needs to serve as the source data for the textbox, and must appear in the first column of the list box. Adjacent, i need the list box to display the data from columns 5, 7, 2, 3, 4 respectively [in that particular order].
As I wrote, I assumed I would be able to adapt an answer to my particular needs, however i haven't managed. If you have the time and its not to difficult, it would be a major help if you would be able to tweek your above answer for my specific table.

Thanks again.

 

[Fluff]

Could you ever have only one row that meats the criteria?

 

[charly1]

Could you ever have only one row that meats the criteria?

Sorry i don't understand your question?

 

[Fluff]

Could you have only one row of data that matches what you key into the textbox?

 

[charly1]

Oh I understand. Yes I could only have 1 matching row. Quite often actually.
Thanks again for your effort

 

[Fluff]

Ok, how about

Private Sub TextBox1_Change()
   Dim Ary As Variant, Rws As Variant
   Dim TBVal As String
   Dim Ws As Worksheet
   
   
   Set Ws = Sheets("Sheet1")
   TBVal = TextBox1.Value
   With Ws.ListObjects("Table1").DataBodyRange
      If TBVal = "" Then
         Ary = Evaluate("choosecols(" & .Address & ",14, 7, 5, 2, 3, 4)")
      Else
         Rws = Filter(Evaluate(Replace("transpose(if(isnumber(search(""" & TBVal & """,@)),row(@)-min(row(@))+1,""X""))", "@", .Columns(14).Address)), "X", False)
         If UBound(Rws) < 0 Then
            Me.ListBox1.List = Array("No matches")
            Exit Sub
         ElseIf UBound(Rws) = 0 Then
            ReDim Preserve Rws(1)
         End If
         Ary = Application.Index(.Value, Application.Transpose(Rws), Array(14, 5, 7, 2, 3, 4))
      End If
   End With
   With Me.ListBox1
      .ColumnCount = UBound(Ary, 2)
      .List = Ary
   End With
End Sub

Private Sub UserForm_Initialize()
   Dim Ary As Variant
   
   With Sheets("Sheet1").ListObjects("Table1").DataBodyRange
      Ary = Evaluate("choosecols(" & .Address & ",14, 7, 5, 2, 3, 4)")
   End With
   With Me.ListBox1
      .ColumnCount = UBound(Ary, 2)
      .List = Ary
   End With
End Sub

 

[charly1]

It works perfectly. Thanks a million. I really appreciate your time.

One slight issue, the textbox may at times also contain somwhere in the data any of the following four characters [ ]"-. When that happens i get an error message, which ime assuming is as a result of the above being noncompatible with string variable. Can the above code be easily tweaked to solve the issue.