Selected Answer
Mussala
The attached revised file allows you to "pick a column from the dropdown on new ComboBox3 then filter the listbox based on TextBox1 value (and CB1/ CB2)".
I've modified your UserForm slightly and made changes to your code (in bold in the extracts below).
Firstly at the top of the UserForm code, I've declared a new variable Srch ( for the Search column):
Option Explicit
Option Compare Text
Private Data, Temp, Srch As Integer
When you launch the form, you won;t see Textbox1 or its labelsince the initialisation code now includes:
Private Sub UserForm_Initialize()
<< existing code>>
' hide textbox1 and label
TextBox1.Visible = False
Label2.Visible = False
End Sub
but they will appear if you chose a Search column in ComboBox3, whose code is:
Private Sub ComboBox3_Change()
' show label2 move cursor to (blank) textbox1
Label2.Visible = True
With TextBox1
.Visible = True
.SetFocus
.Value = ""
End With
End Sub
If you then type a value into the tetxtbox, this runs
Private Sub TextBox1_Change()
' record column chosen by user
Srch = ComboBox3.ListIndex + 3
Call LBoxPop
End Sub
which in turn calls the modified code below:
Private Sub LBoxPop()
<< existing code>>
For i = 2 To UBound(Data)
' filter on value for chosen column (Srch) or 4
If TextBox1.Value = "" Then
crit = Data(i, 4)
Srch = 4
Else
crit = TextBox1.Value
End If
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
' filter on chosen column (Srch)
If Data(i, Srch) 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
<< existing code>>
Note that the addition of a leading wildcard ("*" & crit) means you can put a string from any part of that field e.g. if searching for a CSS, you can now type "202" (instead of "R202") and quickly get results for CSS R202.
Hope this makes sense and does what you want.
