如何根据Textbox值过滤列表框值 [英] How to filter listbox values based on a Textbox value
问题描述
我有一个用户窗体的文本框和列表框。我想根据我在文本框中输入的值来过滤列表框中的值。名为TMP的工作表具有值,我根据文本框更改事件对其进行过滤,但将其添加到列表框时会自动退出。
I have a textbox and a listbox on userform. I want to filter the values in listbox based on the value I enter in Textbox. Sheet named TMP has the values and I filter it based on textbox change event but it quits automatically when adding that values to listbox.
Private Sub Textbox1_Change()
'On Error Resume Next
Dim fCell As Range, MyArr As Variant, i As Long
With TMP
.AutoFilterMode = False
.Range("A1").AutoFilter
.Range("A1").AutoFilter Field:=1, Criteria1:=Me.TextBox1.Value
End With
ListBox1.RowSource = ""
i = 0
For Each fCell In TMP.Range("A1:A" & TMP.Range("A" & TMP.Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
Me.ListBox1.AddItem fCell.Value, i
i = i + 1
Next fCell
End Sub
推荐答案
我确信希望以下一段代码是你正在寻找的。 p>
I sure hope the following piece of code is what you are looking for.
Private Sub Textbox1_Change()
Dim i As Long
Dim arrList As Variant
Me.ListBox1.Clear
If TMP.Range("A" & TMP.Rows.Count).End(xlUp).Row > 1 And Trim(Me.TextBox1.Value) <> vbNullString Then
arrList = TMP.Range("A1:A" & TMP.Range("A" & TMP.Rows.Count).End(xlUp).Row).Value2
For i = LBound(arrList) To UBound(arrList)
If InStr(1, arrList(i, 1), Trim(Me.TextBox1.Value), vbTextCompare) Then
Me.ListBox1.AddItem arrList(i, 1)
End If
Next i
End If
If Me.ListBox1.ListCount = 1 Then Me.ListBox1.Selected(0) = True
End Sub
请注意,此子不使用 AutoFilter
在工作表 TMP
。因此,sub有点快。此外,如果您希望在工作表中过滤数据,则此子将不会删除/更改当前的过滤器设置。
Note that this sub does not make use of the AutoFilter
on the sheet TMP
. Therefore, the sub is a bit faster. Also, if you wish to filter your data on the sheet, this sub won't delete / change your current filter settings.
结尾处的行 If Me.ListBox1.ListCount = 1 Then Me.ListBox1.Selected(0)= True
不是真的必要,而是为了方便起见。它确保如果列表中只有1个项目,该项目将在 ListBox
中自动选择。
The line at the end If Me.ListBox1.ListCount = 1 Then Me.ListBox1.Selected(0) = True
is not really necessary but rather for your convenience. It ensures that the item is automatically selected in the ListBox
if there is only 1 item in the list.
< a href =https://i.stack.imgur.com/0BLGf.gif =nofollow noreferrer>
这篇关于如何根据Textbox值过滤列表框值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!