expand code to search on userform more than column by selection from combobox

[abdelfattah]

Hi guys,

first I would thank @DanteAmor for modifying code more than one time .

so I want expanding the code to make search based on multiple columns .

currently the code will search based on column D , now I add combobox3 and I will populate headers names in combobox3 to select specific column to search

first when select sheet name from combobox1 and select header from combobox3 then will populate data in listbox based on column contains header is matched with combobox3 after this if I select month from comboboxes 2 then will just populate data in listbox for this month based on selected comboboxes 1,3 together

second when write the ID in textbox1 based on combobox3, then will populate data on userform based on selected comboboxes1,2,3 together

third if I don't select combobox 3 and just select combobox1,2 then just filter in listbox based on original code based on combobox1,2 together

fourth if I don't select combobox 3 and just select combobox1,2 , write ID in textbox , then pops up message " until you can search for ID you have to select header from combobox3" and exit sub. so if I try writing ID in textbox1 without select combobox3 then should pops up message " until you can search for ID you have to select header from combobox3" .

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

I hope the experts can help me thanks

 

[abdelfattah]

I hope the expert help me.
thanks