Teach you step by step how to create a dynamic fuzzy matching drop-down menu in Excel

This article brings you relevant knowledge about excel, which mainly introduces related issues about how Excel makes dynamic fuzzy matching drop-down menus. Let’s take a look at it together. I hope it will help Everyone is helpful.

Related learning recommendations: excel tutorial

We know that we can use functions to create fuzzy matching drop-down menus, but the function guy The characteristic is that it is small and smart. If the data volume is slightly larger, the efficiency will be at a critical moment. As we all know, in Excel, to solve complex problems efficiently, you still have to rely on the stupid and stupid VBA. So, today I will share with you how to use VBA to create a more useful dynamic fuzzy matching drop-down menu.

The completed effect demonstration is as follows:

#As shown in the picture above, click on the cell in column A, Excel will automatically pop up a text input box and a list frame. When data is entered in the text box, the data in the list box will be dynamically updated.

1 丨 Production steps

Select the target worksheet, go to [Development Tools] → [Insert] → [ActiveX Control], and insert a text box and a list box. The size and storage location of the control are arbitrary. It doesn't matter what you say anyway. The code will make adjustments by itself later.

It should be noted that if you do not know how to adjust the VBA code, then the name of the text box here must be TextBox1, and the name of the list box must be ListBox1 - normal and In other words, these two names are also the system default.

Keep the target worksheet selected, press the shortcut key to open the VBE editor, and paste the following code into the code window of the current worksheet.

See comments for code analysis

'设置文本框和列表框的大小及位置
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim b As Boolean, arr
    If Target.Column <> 1 Or Target.Row < 2 Then b = True &#39;如果用户选择的单元格不是第1列或者属于第1行
    If Target.Columns.Count > 1 Or Target.Rows.Count > 1 Then b = True &#39;如果用户选择的单元格数量大于1
    If b Then
        ListBox1.Visible = False &#39;不可见
        TextBox1.Visible = False &#39;不可见
        Exit Sub &#39;退出程序
    End If
    With Worksheets("示例") &#39;下拉列表来源内容的所在工作表
        arr = .Range("d2:d" & .Cells(Rows.Count, "d").End(3).Row) &#39;数据源
    End With
    With TextBox1
        .Value = ""
        .Visible = True &#39;可见
        .Top = Target.Top &#39;文本框顶部位置
        .Left = Target.Left &#39;文本框左侧位置
        .Height = Target.Height &#39;文本框高度
        .Width = Target.Width &#39;文本框宽度
        .Activate &#39;激活文本框
    End With
    With ListBox1
        .Visible = True &#39;可
        .Top = Target.Offset(0, 1).Top
        .Left = Target.Offset(0, 1).Left
        .Height = Target.Height * 5
        .Width = Target.Width
        .List = arr &#39;写入数据源数据
    End With
End Sub
&#39;根据文本框的输入值动态匹配数据
Private Sub TextBox1_Change()
    Dim arr, brr, i&, k&
    With Worksheets("示例") &#39;下拉列表来源内容的所在工作表
        arr = .Range("d2:d" & .Cells(Rows.Count, "d").End(3).Row) &#39;数据源
    End With
    If TextBox1.Text = "" Then ListBox1.List = arr: Exit Sub
    ReDim brr(1 To UBound(arr))
    For i = 1 To UBound(arr)
        If InStr(1, arr(i, 1), TextBox1.Text, vbTextCompare) Then  &#39;忽略字母大小写
            k = k + 1
            brr(k) = arr(i, 1)
        End If
    Next
    ListBox1.List = brr &#39;写入匹配后的数据
End Sub
&#39;如果双击列表框的内容则写入活动单元格
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    ActiveCell = ListBox1.Text
    With ListBox1
        .Clear &#39;清空列表框
        .Visible = False
    End With
    With TextBox1
        .Value = ""
        .Visible = False
    End With
End Sub

Finally close VBE and save the current workbook as an xlsm file.
……

2丨Others

If you don’t understand VBA, after following the above steps, you need to replace the “example” in the code with the worksheet where the drop-down list data source is located name, replace the "d2:d" & .Cells(Rows.Count, "d") part with the column where the actual data source is located.

With Worksheets("示例") '下拉列表来源内容的所在工作表
arr = .Range("d2:d" & .Cells(Rows.Count, "d").End(3).Row) '数据源
End With

Related learning recommendations: excel tutorial

The above is the detailed content of Teach you step by step how to create a dynamic fuzzy matching drop-down menu in Excel. For more information, please follow other related articles on the PHP Chinese website!