Krayvent0811
New Member
- Joined
- Apr 29, 2020
- Messages
- 1
- Office Version
- 365
- Platform
- 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
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