vba 如何根据文本框值过滤列表框值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/42879261/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to filter listbox values based on a Textbox value
提问by Rohan
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.
我在用户表单上有一个文本框和一个列表框。我想根据我在文本框中输入的值过滤列表框中的值。名为 TMP 的工作表具有值，我根据文本框更改事件对其进行过滤，但在将该值添加到列表框时它会自动退出。
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
回答by Ralph
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
Note that this sub does not make use of the AutoFilteron 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.
请注意，此 sub 不使用AutoFiltersheet 上的TMP。因此，sub要快一点。此外，如果您希望过滤工作表上的数据，此子项不会删除/更改您当前的过滤器设置。
The line at the end If Me.ListBox1.ListCount = 1 Then Me.ListBox1.Selected(0) = Trueis not really necessary but rather for your convenience. It ensures that the item is automatically selected in the ListBoxif there is only 1 item in the list.
最后的行If Me.ListBox1.ListCount = 1 Then Me.ListBox1.Selected(0) = True并不是真正必要的，而是为了您的方便。它确保ListBox在列表中只有 1 个项目时自动选择该项目。
