add another condition to filter data based on month in listbox on userform

[abdelfattah]

Hi

codes will select the sheet name from combobox1 and populate based on sheet is selected from combobox1 and when write ID based on column D will search within sheet is selected from combobox1 . now I add combobox2 contains months (1,2.......)

what I want when select sheet from combobox 1 and select month(1) from combobox2 then should filter data in list box within selected sheet from combobox1 for just selected month(1) from combobox2 , also should deal with textbox1 when write ID . I mean if select sheet from combobox1 and month from combobox2 and write ID based on column D then should populate data in listbox within sheet & month based on the ID is written in textbox1 .

Option Explicit
Option Compare Text

Private Data, Temp, Crit As String, i As Long, ii As Long, x As Long
Dim WS As Worksheet

Private Sub ComboBox1_Change()
  If ComboBox1.Value = "" Then Exit Sub
  Set WS = Sheets(ComboBox1.Value)
  Call LBoxPop
End Sub

Private Sub TextBox1_Change()
  Crit = TextBox1
  Call LBoxPop
End Sub

Private Sub UserForm_Initialize()
  Crit = ""
  Dim i As Long
  For i = 5 To Sheets.Count
    ComboBox1.AddItem Sheets(i).Name
  Next
 
  If ComboBox1.ListIndex > -1 Then
    Set WS = Sheets(ComboBox1.Value)
    Call LBoxPop
  End If
End Sub

Private Sub LBoxPop()
  Dim i&, ii&
  Dim myFormat(1) As String
 
  Data = WS.Cells(1).CurrentRegion.Value
  x = 0
  myFormat(0) = WS.Cells(2, 8).NumberFormatLocal
  myFormat(1) = WS.Cells(2, 9).NumberFormatLocal
 
  ReDim Temp(1 To UBound(Data, 1), 1 To 10)
 
  For i = 1 To UBound(Data)
    If Data(i, 4) Like Crit & "*" Or Data(i, 4) = "CODE" Then
      x = x + 1
      Temp(x, 2) = Format(Data(x, 2), "DD/MM/YYYY")
      For ii = 1 To 10
        Temp(x, ii) = Data(i, ii)
        If ii >= 8 Then
          Temp(x, ii) = Format$(Data(i, ii), myFormat(1))
        End If
      Next ii
    End If
  Next i
 
  With UserForm1.ListBox1
    .ColumnCount = 10
    .ColumnWidths = "80;80;120;80;60;60;60;60;60;60"
    .List = Temp
  End With
End Sub

 

[abdelfattah]

any help experts?

 

[DanteAmor]

This is how I solve the filter with several criteria.


Replace all your code with the following:

Option Explicit
Option Compare Text

Private Data, Temp
Dim WS As Worksheet

Private Sub ComboBox1_Change()
  Call LBoxPop
End Sub

Private Sub ComboBox2_Change()
  Call LBoxPop
End Sub

Private Sub TextBox1_Change()
  Call LBoxPop
End Sub

Private Sub LBoxPop()
  Dim i&, j&, x&
  Dim myFormat(1) As String, crit As String
  Dim cmb2 As Long
 
  If ComboBox1.ListIndex = -1 Then Exit Sub
  Set WS = Sheets(ComboBox1.Value)
 
  Data = WS.Cells(1).CurrentRegion.Value
  myFormat(0) = WS.Cells(2, 8).NumberFormatLocal
  myFormat(1) = WS.Cells(2, 9).NumberFormatLocal
 
  ReDim Temp(1 To UBound(Data, 1), 1 To 10)
  x = 1
  For j = 1 To 10
    Temp(x, j) = Data(x, j)
  Next
  
  For i = 2 To UBound(Data)
    If TextBox1.Value = "" Then crit = Data(i, 4) Else crit = TextBox1.Value
    If ComboBox2.Value = "" Then cmb2 = Month(Data(i, 2)) Else cmb2 = Val(ComboBox2.Value)
    
    If Data(i, 4) Like crit & "*" And Month(Data(i, 2)) = cmb2 Then
      x = x + 1
      For j = 1 To 10
        Temp(x, j) = Data(i, j)
        If j = 2 Then Temp(x, 2) = Format(Data(i, 2), "DD/MM/YYYY")
        If j >= 8 Then Temp(x, j) = Format$(Data(i, j), myFormat(1))
      Next j
    End If
  Next i
 
  With UserForm1.ListBox1
    .clear
    .ColumnCount = 10
    .ColumnWidths = "80;80;120;80;60;60;60;60;60;60"
    .List = Temp
  End With
End Sub

Private Sub UserForm_Initialize()
  Dim i As Long
  For i = 5 To Sheets.Count
    ComboBox1.AddItem Sheets(i).Name
  Next
  ComboBox2.List = [row(1:12)]
 
  If ComboBox1.ListIndex > -1 Then
    Set WS = Sheets(ComboBox1.Value)
    Call LBoxPop
  End If
End Sub

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[abdelfattah]

excellent!
thank you so much .