Selected Answer
Mussa
I don't think you need the line you added (Me.ListBox1.List = a) but you need to dissociate Listbox1 from its RowSource before your clear it (then filter).
I've renamed your Public variable "a" to Arr (for easier reading) then made these changes:
1) Modified the UserForm1 to add labels (espeically "Fieldname" since my guess if that you will change the ID-CC label to a ComboBox (to allow other fields to be searched)
2) Changed the background macros as follows (see lines in bold/ comments):
Private Sub ComboBox1_Change()
With ComboBox1
If .Value = "" Then
'Me.ListBox1.List = Arr
Exit Sub
End If
If .ListIndex = -1 Then Exit Sub
ListBox1.RowSource = ""
ListBox1.Clear
ListBox1.RowSource = "'" & Sheets(.Value).Name & "'!" & Sheets(.Value).Range("A1", Sheets(.Value).Range("H" & Rows.Count).End(3)).Address
TextBox1 = "" 'added- clear search box
End With
End Sub
so when you change sheets, there's a full list and no misleading filter text.
Also:
Sub FilterData()
Dim i As Long, ii As Long, n As Long
'Me.ListBox1.List = Arr
If Me.TextBox1 = "" Then Exit Sub
With Me.ListBox1
If .ListCount > 0 Then ' ## added
.RowSource = "" ' ## added
.Clear
End If
For i = 0 To UBound(Arr, 1)
If UCase$(Arr(i, 3)) Like "*" & UCase$(Me.TextBox1) & "*" Then ' ## changed
.AddItem
.List(n, 0) = n + 1
For ii = 1 To UBound(Arr, 2)
.List(n, ii) = Arr(i, ii)
Next
n = n + 1
End If
Next
End With
End Sub
where the
If . ListBox1... statement checks if there's a list, clears the row source to allow the clear (and the subsequent re-building of Listbox1 with filterring). The line starting
If UCase$(Arr(i, 3)) adds wildcards around the TextBox1 search value so that you can now type "01" (say) and only the entries containing that string will be displayed. I hope this is better/ faster for your users.
In anticipation of you using a "Fieldname" ComboBox to search other fields, I've extneded the listbox contents (on change of Sheet source) to include the header row to remind users of what text string they might be searching in.
Hope this works for you.
