从TextBox文本中筛选ListBox Excel中的仅匹配结果 [英] Filter Only matching results in ListBox Excel from TextBox text

问题描述

我有一个带有文本框和列表框的用户表单.我希望用户能够在文本框中输入文本，并让列表框根据输入的内容过滤结果.

I have a user form with a textbox and a listbox. I would like a user to be able to enter text into the textbox, and have the listbox filter results based on their typing.

到目前为止，我已经设法使ListBox突出显示列表中的匹配结果，但没有过滤掉不匹配的结果.我还遇到了我的代码无法识别多个匹配记录的问题，不确定是否需要添加什么才能使这种情况发生.

So far, I have managed to get the ListBox to highlight matching results in the list, but not filter out results that dont match. I have also run into the issue of my code not identifying multiple matching records, not sure what I need to add to get this to happen.

Private Sub TextBox3_Change()
        'searches ListBox3 for match and hightlights result. Need to filter results.
    Dim i As Long
    Dim sFind As String

    sFind = Me.TextBox3.Text

    If Len(sFind) = 0 Then
        Me.ListBox3.ListIndex = -1
        Me.ListBox3.TopIndex = 0
    Else
        For i = 0 To Me.ListBox3.ListCount - 1
            If UCase(Left(Me.ListBox3.List(i), Len(sFind))) = UCase(sFind) Then
                Me.ListBox3.TopIndex = i
                Me.ListBox3.ListIndex = i
                Exit For
            End If
        Next i
    End If
End Sub

推荐答案

尝试使用此代码在退出textbox3时有效，否则在键入时会进行一些过滤，并可能导致错误.

Try using this code that works when you exit textbox3, otherwise it will make some filtering while typing and can bring errors.

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
For i = ListBox1.ListCount - 1 To 0 Step -1
    If Not ListBox1.List(i) = TextBox3 Then ListBox1.RemoveItem (i)
Next i
End Sub

此循环是递归循环，否则会出现错误.

And the loop is made with a recursive loop, otherwise an error appear.

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
For i = ListBox1.ListCount - 1 To 0 Step -1
    If InStr(1, ListBox1.List(i), TextBox3) = 0 Then ListBox1.RemoveItem (i)
Next i
End Sub

找到了更好的代码来过滤列表框.

这篇关于从TextBox文本中筛选ListBox Excel中的仅匹配结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！