Display data of Specific Sheets on Listbox Userform

[sofas]

Hello. After searching, I found this code to search and display data inside the listbox. I want to modify it or get a different code. It enables me to display the data of 3 Excel sheets from inside the workbook on the list. I tried this, but to no avail.

Private Sub TextBox1_Change()
  
    Dim ws          As Worksheet
    Dim rng         As Range
    Dim r           As Long, c As Long, Lastrow As Long, r1 As Long
    Dim Search      As String
    Dim FilterArr()   As Variant
  
    Search = Me.TextBox1.Value
  
    Set ws = ThisWorkbook.Worksheets("Sheet2")
  
    Lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
  
    'size worksheet data range
    Set rng = ws.Cells(2, 1).Resize(Lastrow, 28)
  
    arr = rng.Value2
  
    ReDim FilterArr(1 To rng.Rows.Count, 1 To rng.Columns.Count)
  
    With Me.ListBox1
        'disconnect rowsource
        .RowSource = ""
        .ColumnHeads = False
      
        'size listbox
        .ColumnCount = rng.Columns.Count
      
        .Clear
      
        If Len(Search) > 0 Then
          
            For r = 1 To UBound(arr, 1)
                If UCase(arr(r, 5)) Like UCase(Search) & "*" Then
                    r1 = r1 + 1
                    For c = 1 To UBound(arr, 2)
                        FilterArr(r1, c) = arr(r, c)
                    Next c
                End If
            Next r
          
            .List = ResizeArray(FilterArr, r1)
          
        Else
          
            'display full list
            .List = arr
          
            'or use rowsource
            're-connect rowsource to display all data with column heads
            .RowSource = ws.Name & "!" & rng.Address
            .ColumnHeads = True
          
        End If
      
    End With
  
End Sub
Function ResizeArray(ByVal arr As Variant, ByVal RowsCount As Long) As Variant
    Dim r       As Long, c As Long
    Dim Arr2()  As Variant
    
     If RowsCount > 0 Then
     'size array to match filtered data
        ReDim Arr2(1 To RowsCount, 1 To UBound(arr, 2))
        For r = 1 To RowsCount
            For c = 1 To UBound(arr, 2)
        'pass matching elements of arr to arr2
            Arr2(r, c) = arr(r, c)
            Next c
        Next
     End If
    ResizeArray = IIf(RowsCount > 0, Arr2, Array("No Match Found"))
End Function

Dim Rng As Range
Dim r As Long, c As Long, Lastrow As Long, r1 As Long
Dim FilterArr() As Variant
Dim wb As Workbook
Set wb = ThisWorkbook

Me.ListBox1.Clear
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
    Select Case ws.Name

        Case "Sheet1", "Sheet2", "Sheet3"
        
        Lastrow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
             Set Rng = ws.Cells(2, 1).Resize(Lastrow, 11)
            

    arr = Rng.Value2
    
    ReDim FilterArr(1 To Rng.Rows.Count, 1 To Rng.Columns.Count)
    
    With Me.ListBox1
        .RowSource = ""
        .ColumnHeads = False
        .ColumnCount = Rng.Columns.Count
         For r = 1 To UBound(arr, 1)
                    r1 = r1 + 1
                    For c = 1 To UBound(arr, 2)
                        FilterArr(r1, c) = arr(r, c)
                    Next c
        
            Next r
            
            .List = ResizeArray(FilterArr, r1)
            
       .List = arr
       .RowSource = ws.Name & "!" & Rng.Address
        .ColumnHeads = True
        End With
        
 End Select
Next ws
End Sub

 

[Akuini]

@sofas
Sorry, the code is flawed.
Please, replace this part:

Private Sub ComboBox1_Change()
    Call to_filter_cbo(1, vList1)
End Sub

Private Sub ComboBox2_Change()
    Call to_filter_cbo(2, vList2)
End Sub

Private Sub ComboBox3_Change()
    Call to_filter_cbo(3, vList3)
End Sub

Private Sub ComboBox4_Change()
    Call to_filter_cbo(4, vList3)
End Sub

with this:

Private Sub ComboBox1_Change()
    If ComboBox1 = "" Then
        ComboBox1.List = vList1
    Else
        Call to_filter_cbo(1, vList1)
    End If
End Sub

Private Sub ComboBox2_Change()
    If ComboBox2 = "" Then
        ComboBox2.List = vList2
    Else
        Call to_filter_cbo(2, vList2)
    End If
End Sub

Private Sub ComboBox3_Change()
    If ComboBox3 = "" Then
        ComboBox3.List = vList3
    Else
        Call to_filter_cbo(3, vList3)
    End If

End Sub

Private Sub ComboBox4_Change()
    If ComboBox4 = "" Then
        ComboBox4.List = vList3
    Else
        Call to_filter_cbo(4, vList3)
    End If
End Sub

 

[Akuini]

@sofas
I've made some adjustments in the code to make it work better. So, use this one instead:
sofas - TEST FILTRE DATA 2.xlsm