时间戳

首页 > 解决方案 > 键入时具有自动完成和搜索功能的 VBA 组合框

excel - 键入时具有自动完成和搜索功能的 VBA 组合框

问题描述

我有下面的代码，它创建了一个带有自动完成功能的用户表单，我从另一个网站复制了它。我想对此进行修改以包括“键入时搜索”功能。例如：

组合框引用的表格是 1 列，包括以下项目：

chevy truck
ford truck
truck
chevy car
ford car
car

目前，当用户键入“卡车”时，唯一的结果和建议是“卡车”，不会显示“雪佛兰卡车”

当用户搜索“卡车”时，我希望下拉列表显示类似的内容

truck
chevy truck
ford truck

或者如果用户键入“tr”，列表将显示：

truck
chevy truck
ford truck

用户类型'che'...列表显示：

chevy truck
chevy car

等等

Private Sub OEM_Change()
Dim x, dict
Dim i As Long
Dim str As String
'x = this is where i need help.
Set dict = CreateObject("scripting.dictionary")
str = Me.OEM.Value
    If str <> "" Then
        For i = 1 To UBound(x, 1)
            If InStr(LCase(x(i, 1)), LCase(str)) > 0 Then
                dict.Item(x(i, 1)) = ""
            End If
        Next i
    Me.OEM.List = dict.keys
    Else
    Me.OEM.List = x
    End If
    Me.OEM.DropDown

End Sub

Private Sub UserForm_Initialize()

With Vertical
     .AddItem "vertical1"
     .AddItem "vertical2"
     .AddItem "vertical3"
     .AddItem "vertical4"
     .AddItem "vertical5"
End With

End Sub

Private Sub Vertical_Change()

Dim index As Integer
 index = Vertical.ListIndex

Select Case index
     Case Is = 0
         With OEM
             .RowSource = "Namedrange1"
         End With
     Case Is = 1
         With OEM
             .RowSource = "Namedrange2"
         End With
     Case Is = 2
         With OEM
            .RowSource = "Namedrange3"
         End With
    Case Is = 3
         With OEM
            .RowSource = "Namedrange4"
         End With
    Case Is = 4
         With OEM
            .RowSource = "Namedrange5"
         End With

End Select

End Sub

供参考：其中一些代码来自这个线程

带有用户表单上的建议列表的可搜索组合框

标签： excelvbafiltercomboboxautocomplete

解决方案

好吧，伙计们，我想通了。我将另一个变量调暗为“p”。p = vertical.value，然后 x = worksheets("sheet2").range(p).value。完美运行。感谢大家的帮助

推荐阅读