代码之家 › 专栏 › 技术社区 › Andriano Crs

如何使用VBA从下拉列表中筛选列表

dropdown filtering excel vba

Andriano Crs · 技术社区 · 7 年前

对不起,我需要一些关于使用VBA筛选下拉列表的帮助我有一个脚本可以使用VBA创建下拉列表,如下所示

Dim LRow As Long 
Dim ws As Worksheet
Dim Rng As Range

Set ws = ThisWorkbook.Worksheets("Input_PH")

LRow = Worksheets("PH").Range("A" & Worksheets("PH").Rows.Count).End(xlUp).Row
Set Rng = ws.Range("D5")
With Rng.Validation
    .Delete
   .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="='PH'!$A$2:$A$" & LRow

现在,从下拉列表中,如何使用我在其他列中输入的值进行筛选,

例如E5,我从下拉列表中有一个列表是“exam”、“exit”、“leave”、“gone”,当我在E5列中输入“ex”时,D5中的下拉列表将被过滤并变为“exam”、“exit”

我希望有人能帮我。谢谢

1 回复 | 直到 7 年前

Neal 7 年前

据我所知。。。

Excel工作表(“PH”)中有一个列表。
该列表在配置为下拉列表的工作表(“Input\u PH”)中的单元格D5上填充验证列表。
您希望通过工作表单元格E5中的值(“Input\u PH”)过滤验证列表。

解决方案

创建一个返回筛选列表的函数。
更新E5时,将过滤列表用于单元格D5上的验证标准。

为了让生活更轻松,请将工作表上的未过滤列表命名为“PH”。称之为“drop\u list”(不带引号)。这避免了在上面的代码段中需要LRow。

过滤器功能

将此函数放在VBA模块中

Function filter_list(the_filter, the_list)
    ' compile a list of filtered values
    Dim filtered_values
    For Each list_item In the_list
        If InStr(LCase(list_item), LCase(the_filter)) > 0 Or the_filter = "" Then
            filtered_values = filtered_values & list_item & ","
        End If
    Next
     ' default value if no match
    filter_list = ""
    ' remove trailing ,
    If Len(filtered_values) > 1 Then filter_list = Left(filtered_values, Len(filtered_values) - 1)
End Function

验证更新方法

在VBA编辑器中,将此sub放入工作表(“Input\u PH”)。它需要转到此处以利用Worksheet\u Change事件触发器。这不是您的代码片段。

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range
    Dim the_filter As Range, the_list As Range
    Set the_filter = Me.Range("E5")

    If Target.Address = the_filter.Address Then
        Set Rng = Me.Range("D5")
        Set the_list = ThisWorkbook.Names("drop_list").RefersToRange
        filtered_list = filter_list(the_filter.Cells(1).Value, the_list.Value)
        With Rng.Validation
            .Delete
            If filtered_list <> "" Then .Add Type:=xlValidateList, Formula1:=filtered_list
        End With
    End If
End Sub

当在工作表上输入值(“Input\u PH”)时,将触发工作表\u Change。如果更改发生在E5中,则更新D5上的验证过滤器。如果E5为空,验证列表将填充完整列表。无效值将删除验证。