excel - 如何在 Excel VBA 中创建一个搜索过滤器，在键入单词时在 ListBox 中显示搜索结果？

Question

我正在尝试在我的用户窗体中进行数周的高级搜索，它将在输入值时过滤并在 ListBox 上显示结果。但不知何故，我作为过滤器的 ComboBox 已经有一个下拉功能。

我不知道我怎样才能让它像我想要的那样。

我的用户窗体包含 8 列。

这是 ComboBox 过滤器的现有代码

Private Sub cmbSearch_Change()

    'The function of this code below is for the user to click a value from the ComboBox and then the result will be displayed on the TextBoxes and ListBox.
    x = Sheets("DATA STOCK").Range("A" & Rows.Count).End(xlUp).Row
        For y = 2 To x
            If Sheets("DATA STOCK").Cells(y, 1).Text = cmbSearch.Value Then
                cmbSchema.Text = Sheets("DATA STOCK").Cells(y, 1)
                cmbEnvironment.Text = Sheets("DATA STOCK").Cells(y, 2)
                cmbHost.Text = Sheets("DATA STOCK").Cells(y, 3)
                cmbIP.Text = Sheets("DATA STOCK").Cells(y, 4)
                cmbAccessible.Text = Sheets("DATA STOCK").Cells(y, 5)
                cmbLast.Text = Sheets("DATA STOCK").Cells(y, 6)
                cmbConfirmation.Text = Sheets("DATA STOCK").Cells(y, 7)
                cmbProjects.Text = Sheets("DATA STOCK").Cells(y, 8)

                UserForm1.listHeader.RowSource = "A" + CStr(y) + ": H" + CStr(y)
                Exit For
            End If
        Next y

End Sub

预期结果：

1. 用户在 ComboBox 中输入单词（我选择了 ComboBox 作为过滤器，因为它的下拉功能）
2. 当用户键入时，它会将结果显示到 ListBox。

问题是我不知道如何创建这种搜索过滤器，即使我的 ComboBox 中已经有一个下拉功能，是否有可能

标签： excelvbauserform

Answer 1

看到你已经为此工作了数周。

我已经重构了您的表单代码并实现了您一直在寻找的功能。

作为我对您其他问题的另一个回答，在我看来，向列表框中添加和删除项目比使用 excel 范围更容易。（如何在我的代码中修复这个不允许我更新 excel 用户表单中的其他列的错误？）

重要说明： - 我已将工作表中的数据转换为 Excel 结构化表格 (Ctrl + T) - 我使用了您以前的文件之一，因此您在表格中的信息可能已过时 - 测试我也修改了一些数据 - 我建议您复制并粘贴您最近的数据并将其替换到表格中

在这里您可以根据您的数据下载文件： https ://github.com/rdiazjimenez/excel-vba-userform-b​​asic-listbox-demo/blob/master/MDM_DB_Checking_09122018_RD.xlsm

---

我介绍了将 Excel 数据加载到用户窗体内的列表框中的基本操作（创建、读取、更新、删除和搜索/过滤）。

---

这是表单背后的代码：

Option Explicit

    ' Code updated
    Private Sub btnDelete_Click()

        Application.EnableEvents = False

        Call mCode.Delete

        Application.EnableEvents = True

    End Sub

    ' Code updated
    Private Sub btnView_Click()

        Application.EnableEvents = False
        Call mCode.Read
        Application.EnableEvents = True

    End Sub

    ' Code updated
    Private Sub cmbAdd_Click()

        Application.EnableEvents = False

        Call mCode.Create

        Application.EnableEvents = True

    End Sub

    ' Code updated
    Private Sub cmbClearFields_Click()

        Application.EnableEvents = False

        Call mCode.ClearControls

        Application.EnableEvents = True

    End Sub

    ' Code updated
    Private Sub cmbSearch_Change()

        Application.EnableEvents = False

        Call FilterList(Me.listHeader, Me.cmbSearch.Text)

        Application.EnableEvents = True

    End Sub

    ' Code updated
    Private Sub cmbUpdate_Click()

        Application.EnableEvents = False

        Call mCode.Update

        Application.EnableEvents = True

    End Sub

    ' Code updated
    Private Sub CommandButton5_Click()

        Application.EnableEvents = False
        Call mCode.ClearList
        Application.EnableEvents = True

    End Sub

    ' Code from this event was removed
        Private Sub listHeader_Click()

        End Sub

    ' Code added
    Private Sub listHeader_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
        Application.EnableEvents = False

        Call mCode.LoadControls

        Application.EnableEvents = True

    End Sub

    ' Code partially updated
        Private Sub UserForm_Initialize()

            Me.cmbSearch.List = ThisWorkbook.Sheets("PRESTAGE DB").ListObjects("TableData").ListColumns(1).DataBodyRange.Value

            Me.cmbEnvironment.AddItem "DEV"
            Me.cmbEnvironment.AddItem "UAT"
            Me.cmbEnvironment.AddItem "SIT"
            Me.cmbEnvironment.AddItem "QA"
            Me.cmbEnvironment.AddItem "PROD"

            Me.cmbAccessible.AddItem "Y"
            Me.cmbAccessible.AddItem "N"

            Me.cmbIP.AddItem "1521"

            Me.cmbProjects.AddItem "DP - proposed for DEV/SIT"
            Me.cmbProjects.AddItem "PH EFUSE SIT"
            Me.cmbProjects.AddItem "MyAXA SG DEV/DIT"


        End Sub

这是一个名为 mCode 的模块中的代码：

Option Explicit

    ' Global variables
    Const sheetName As String = "PRESTAGE DB"
    Const tableName As String = "TableData"

    Public Sub ShowUserForm()

        oUserForm.Show

    End Sub


    Public Sub Read()
        ' Comments: Loads the data from an excel table (listobject) into a listbox located inside a userform
        ' Params  :
        ' Notes   : Adapt the initialize variables section
        ' Created : 2019/01/25 RD www.ricardodiaz.co
        ' Modified:


        ' Define objects variables
        Dim myUserForm As oUserForm ' Note: you're defining the variable as the class of the userform. This gives you access to the userform's controls later
        Dim myListObject As Excel.listObject
        Dim myRange As Excel.Range

        ' Define other variables
        Dim columnCount As Integer

        Dim selectedItem As Integer
        Dim rowCounter As Long
        Dim columnCounter As Integer

        '''''''' Initialize objects ''''''''

        ' Init the userform
        ' Note: When you initialize it directly with the name of the form, you can access the controls of the userform too
        Set myUserForm = oUserForm

        ' Load the data from the Excel table into a range variable
        ' Note: It's safer to refer to thisworkbook
        Set myListObject = ThisWorkbook.Worksheets(sheetName).ListObjects(tableName)




        '''''''' Initialize variables ''''''''
        myUserForm.listHeader.ColumnWidths = "130 pt;60 pt;82 pt;55 pt;70 pt;195 pt;170 pt;130 pt"

        ' Set the number of columns to the same of the table in the Excel sheet
        columnCount = myListObject.ListColumns.Count

        ' Get the current selected item
        selectedItem = myUserForm.listHeader.ListIndex ' this returns -1 if none is selected

        ' Clear the listbox contents
        Call mCode.ClearList

        ' Set the number of columns to load into the listbox
        myUserForm.listHeader.columnCount = columnCount

        ' Loop through each row and load it into the listbox
        ' Note: begins with 2 because the first row are the table headers
        For rowCounter = 2 To myListObject.Range.Rows.Count
            With myUserForm.listHeader

                .AddItem

                ' Load value of each column in the table row
                For columnCounter = 0 To columnCount

                    .List(rowCounter - 2, columnCounter) = myListObject.Range.Cells(rowCounter, columnCounter + 1).Value

                Next columnCounter

            End With
        Next

        ' Select previously selected row
        If selectedItem < myUserForm.listHeader.ListCount Then
            myUserForm.listHeader.ListIndex = selectedItem
        End If


        ' Clean up objects
        Set myListObject = Nothing
        Set myUserForm = Nothing

    End Sub

    Public Sub Create()
        ' Comments: Adds a new row with the data entered by the user and reloads the listbox inside the userform
        ' Params  :
        ' Notes   : Adapt the initialize variables section
        ' Created : 2019/01/25 RD www.ricardodiaz.co
        ' Modified:

        ' Define objects variables
        Dim myUserForm As oUserForm ' Note: you're defining the variable as the class of the userform. This gives you access to the userform's controls later
        Dim myListObject As Excel.listObject
        Dim myListRow As Excel.listRow


        '''''''' Initialize objects ''''''''

        ' Init the userform
        ' Note: When you initialize it directly with the name of the form, you can access the controls of the userform too
        Set myUserForm = oUserForm

        ' Add the information to the Excel table
        Set myListObject = ThisWorkbook.Worksheets(sheetName).ListObjects(tableName)

        ' Validate if all the information is correct
        If myUserForm.cmbEnvironment.Text = vbNullString _
            Or myUserForm.cmbHost.Text = vbNullString _
            Or myUserForm.cmbIP.Text = vbNullString _
            Or myUserForm.cmbAccessible.Text = vbNullString _
            Or myUserForm.cmbLast.Text = vbNullString Then

            MsgBox "Some fields cannot be blank!", vbCritical, "Data Missing"

            Exit Sub

        End If

        ' Add a blank row at the end of the Excel table
        Set myListRow = myListObject.ListRows.Add

        ' Set the information into de excel table
        With myListRow
            .Range(1) = myUserForm.cmbSchema.Text
            .Range(2) = myUserForm.cmbEnvironment.Text
            .Range(3) = myUserForm.cmbHost.Text
            .Range(4) = myUserForm.cmbIP.Text
            .Range(5) = myUserForm.cmbAccessible.Text
            .Range(6) = myUserForm.cmbLast.Text
            .Range(7) = myUserForm.cmbConfirmation.Text
            .Range(8) = myUserForm.cmbProjects.Text
        End With

        MsgBox "Data Added!"

        ' Reload the data into the listbox
        Call mCode.Read

        ' Select the last item in the listbox
        myUserForm.listHeader.ListIndex = myUserForm.listHeader.ListCount - 1

        ' Clear control's contents
        Call ClearControls ' Note that this is a private procedure inside the mCode module

        ' Clean up objects
        Set myListRow = Nothing
        Set myListObject = Nothing
        Set myUserForm = Nothing

    End Sub

    Public Sub Update()
        ' Comments: Updates a row with the data entered by the user and reloads the listbox inside the userform
        ' Params  :
        ' Notes   : Adapt the initialize variables section
        ' Created : 2019/01/25 RD www.ricardodiaz.co
        ' Modified:

        ' Define objects variables
        Dim myUserForm As oUserForm ' Note: you're defining the variable as the class of the userform. This gives you access to the userform's controls later
        Dim myListObject As Excel.listObject
        Dim myListRow As Excel.listRow

        ' Define variables
        Dim selectedItem As Integer



        '''''''' Initialize objects ''''''''

        ' Init the userform
        ' Note: When you initialize it directly with the name of the form, you can access the controls of the userform too
        Set myUserForm = oUserForm

        ' Add the information to the Excel table
        Set myListObject = ThisWorkbook.Worksheets(sheetName).ListObjects(tableName)



        ' Define selected row number
        selectedItem = myUserForm.listHeader.ListIndex + 1

        ' Exit if there are no other rows
        If selectedItem = 0 Then
            MsgBox "There are no rows left!"
            Exit Sub
        End If

        ' Initialize the row at the end of the Excel table
        Set myListRow = myListObject.ListRows(selectedItem)


        ' the following section is exactly as the Create procedure, so you theorically could make just one procedure for Create and Update

        ' Set the information into de excel table
        With myListRow
            .Range(2) = myUserForm.cmbEnvironment.Text
            .Range(3) = myUserForm.cmbHost.Text
            .Range(4) = myUserForm.cmbIP.Text
            .Range(5) = myUserForm.cmbAccessible.Text
            .Range(6) = myUserForm.cmbLast.Text
            .Range(7) = myUserForm.cmbConfirmation.Text
            .Range(8) = myUserForm.cmbProjects.Text
        End With

        ' Reload the data into the listbox
        Call mCode.Read

        ' Select the updated item in the listbox
        myUserForm.listHeader.ListIndex = selectedItem - 1

        MsgBox "Data Updated!"

        ' Clear control's contents
        Call ClearControls ' Note that this is a private procedure inside the mCode module

        ' Clean up objects
        Set myListRow = Nothing
        Set myListObject = Nothing
        Set myUserForm = Nothing

    End Sub

    Public Sub Delete()
        ' Comments: Deletes a row with the data entered by the user and reloads the listbox inside the userform
        ' Params  :
        ' Notes   : Adapt the initialize variables section
        ' Created : 2019/01/25 RD www.ricardodiaz.co
        ' Modified:

        ' Define objects variables
        Dim myUserForm As oUserForm ' Note: you're defining the variable as the class of the userform. This gives you access to the userform's controls later
        Dim myListObject As Excel.listObject
        Dim myListRow As Excel.listRow

        ' Define variables
        Dim selectedItem As Integer

        '''''''' Initialize objects ''''''''

        ' Init the userform
        ' Note: When you initialize it directly with the name of the form, you can access the controls of the userform too
        Set myUserForm = oUserForm

        ' Add the information to the Excel table
        Set myListObject = ThisWorkbook.Worksheets(sheetName).ListObjects(tableName)

        ' Define selected row number
        selectedItem = myUserForm.listHeader.ListIndex + 1

        ' Exit if there are no other rows
        If selectedItem = 0 Then
            MsgBox "There are no rows left or you didn't select a valid row!"
            Exit Sub
        End If

        If MsgBox("Are you sure you want to delete this row?", vbYesNo + vbQuestion, "Yes") = vbNo Then

            Exit Sub

        End If

        ' Initialize the row at the end of the Excel table
        Set myListRow = myListObject.ListRows(selectedItem)

        ' Delete the row
        myListRow.Delete

        ' Reload the data into the listbox
        Call mCode.Read

        ' Select the next item in the listbox
        myUserForm.listHeader.ListIndex = WorksheetFunction.Min(selectedItem - 1, myUserForm.listHeader.ListCount) - 1

        ' Clean up objects
        Set myListRow = Nothing
        Set myListObject = Nothing
        Set myUserForm = Nothing

    End Sub
    Public Sub ClearList()
        ' Comments: Clear the listbox

        ' Define objects variables
        Dim myUserForm As oUserForm

        ' Init the userform
        ' Note: When you initialize it directly with the name of the form, you can access the controls of the userform too
        Set myUserForm = oUserForm


        myUserForm.listHeader.Clear

    End Sub
    Public Sub LoadControls()
        ' Comments: Loads the selected row's data into the controls

        ' Define objects variables
        Dim myUserForm As oUserForm

        Dim selectedItem As Integer

        ' Init the userform
        ' Note: When you initialize it directly with the name of the form, you can access the controls of the userform too
        Set myUserForm = oUserForm

        ' Get the row of the selected item in the listbox
        selectedItem = myUserForm.listHeader.ListIndex

        ' Set the control's text to each column of the selected item
        myUserForm.cmbSchema.Value = myUserForm.listHeader.List(selectedItem, 0)
        myUserForm.cmbEnvironment.Value = myUserForm.listHeader.List(selectedItem, 1)
        myUserForm.cmbHost.Value = myUserForm.listHeader.List(selectedItem, 2)
        myUserForm.cmbIP.Value = myUserForm.listHeader.List(selectedItem, 3)
        myUserForm.cmbAccessible.Value = myUserForm.listHeader.List(selectedItem, 4)
        myUserForm.cmbLast.Value = myUserForm.listHeader.List(selectedItem, 5)
        myUserForm.cmbConfirmation.Value = myUserForm.listHeader.List(selectedItem, 6)
        myUserForm.cmbProjects.Value = myUserForm.listHeader.List(selectedItem, 7)

        ' Clean up objects
        Set myUserForm = Nothing

    End Sub

    Public Sub ClearControls()
        ' Comments: Reset controls to empty strings

        ' Define objects variables
        Dim myUserForm As oUserForm

        ' Init the userform
        ' Note: When you initialize it directly with the name of the form, you can access the controls of the userform too
        Set myUserForm = oUserForm

        ' Clear the controls
        myUserForm.cmbSchema.Text = vbNullString
        myUserForm.cmbEnvironment.Text = vbNullString
        myUserForm.cmbHost.Text = vbNullString
        myUserForm.cmbIP.Text = vbNullString
        myUserForm.cmbAccessible.Text = vbNullString
        myUserForm.cmbLast.Text = vbNullString
        myUserForm.cmbConfirmation.Text = vbNullString
        myUserForm.cmbProjects.Text = vbNullString

        ' Clean up objects
        Set myUserForm = Nothing

    End Sub

    Public Sub FilterList(oLb As MSForms.ListBox, strFiltro As String)


        Dim columnCounter As Integer
        Dim listString As String

        Dim rowCounter As Integer

        oLb.ListIndex = -1

        ' Read the whole list
        Call mCode.Read

        ' Remove unmatching items
        For rowCounter = oLb.ListCount - 1 To 0 Step -1

            listString = vbNullString

            ' Concat the list columns values in one string
            For columnCounter = 0 To oLb.columnCount

                listString = listString & oLb.Column(columnCounter, rowCounter)

            Next columnCounter

            If InStr(1, listString, strFiltro, 1) = 0 Then

                ' Remove items that don't match
                oLb.RemoveItem rowCounter

            End If

        Next

    End Sub