How to display filtered data on a listbox

Krayvent0811

New Member
Joined
Apr 29, 2020
Messages
1
Office Version
  1. 365
Platform
  1. Windows
Hello People,
I am a begginer learning VBA currently working on a project that involves filtering data, I'm trying to filter a data stored in one specific sheet,
this data was previously define as a "Table" so I could easily define my ListBox1 RowSource as the name of the specific "Table". The autofilter
works fine the problems arises when I try to display the filtered "Table" on the ListBox it just doesn't work.
Could you please advise me how to accomplish it.
The program that follows the filter after using a command button 7 (Search button) is the following.

(Papier is a variable set for the worksheet being used, there are 4 warehouses with diferent material so the data is store in different sheets, choosing the Warehouse will change the number of the sheet)

Private Sub CommandButton7_Click()
Dim ArrList As Variant
Dim NP1, NP2, NP3 As String
Dim Num As Integer
Dim Data


On Error GoTo SinFoto

'Assing parameters for the search depending on the criteria : Part Number,Common Code, Description, Manufacturer, etc.
Select Case C
Case Is = 1 'Part Number
NP1 = "A"
NP2 = "A1:B"
NP3 = "A2"
Case Is = 2 'Common Code
NP1 = "B"
NP2 = "B1:B"
NP3 = "B2"
Case Is = 3 'Description
NP1 = "C"
NP2 = "C1:C"
NP3 = "C2"
Case Is = 4 'Manufacturer
NP1 = "D"
NP2 = "D1:D"
NP3 = "D2"
Case Is = 13 'Usage
NP1 = "M"
NP2 = "M1:M"
NP3 = "M2"
Case Is = 14 'Location
NP1 = "N"
NP2 = "N1:N"
NP3 = "N2"
Case Is = 15 'Type of material
NP1 = "O"
NP2 = "O1:O"
NP3 = "O2"
End Select

'----------------------------------- DOESN'T DISPLAYED FILTERED SEARCH ON LISTBOX1 --------------------------------
'Searching text based on TextBox10
Papier.Range(NP3).AutoFilter Field:=C, Criteria1:=Me.TextBox10.Value & "*", Operator:=xlFilterValues
ListBox1.RowSource = ""
ListBox1.RowSource = Papier.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Address
'--------------------------------------------------------------------------------------------------------------------------------

'----------------------------------- TRYAL WHEN CLICKING ON ROW IN LIST BOX, THE LIST INDEX DOESN'T CORRESPON WITH THE SEARCHED ITEM ----------
'Searching text based on TextBox10
'If Papier.Range(NP1 & Papier.Rows.Count).End(xlUp).Row > 1 And Trim(TextBox10.Value) <> vbNullString Then
' ArrList = Papier.Range(NP2 & Papier.Range(NP1 & Papier.Rows.Count).End(xlUp).Row).Value2
' For i = LBound(ArrList) To UBound(ArrList)
' If InStr(1, ArrList(i, 1), Trim(TextBox10.Value), vbTextCompare) Then
' ListBox1.AddItem ArrList(i, 1)
' Me.ListBox1.List(Me.ListBox1.ListCount - 1, 0) = Papier.Cells(i, 1)
' Me.ListBox1.List(Me.ListBox1.ListCount - 1, 1) = Papier.Cells(i, 2)
' Me.ListBox1.List(Me.ListBox1.ListCount - 1, 2) = Papier.Cells(i, 3)
' Me.ListBox1.List(Me.ListBox1.ListCount - 1, 3) = Papier.Cells(i, 4)
' Me.ListBox1.List(Me.ListBox1.ListCount - 1, 4) = Papier.Cells(i, 12)
' Me.ListBox1.List(Me.ListBox1.ListCount - 1, 5) = Papier.Cells(i, 13)
' Me.ListBox1.List(Me.ListBox1.ListCount - 1, 6) = Papier.Cells(i, 14)
' Me.ListBox1.List(Me.ListBox1.ListCount - 1, 7) = Papier.Cells(i, 15)
' 'Me.ListBox1.List(Me.ListBox1.ListCount - 1, 8) = Papier.Cells(i, 9)
' End If
' Next i
'End If
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

SinFoto:
If Err = 53 Then
Image1.Picture = LoadPicture(ActiveWorkbook.Path & "\Pictures\" & "no_picture" & ".jpg")
End If

End Sub
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Forum statistics

Threads
1,223,237
Messages
6,170,930
Members
452,367
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top