excel - VBA 组合框，在您键入时自动完成和搜索

Question

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

组合框引用的表格是 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

供引用:部分代码来自该线程

searchable combo box with a list of sugggestion on a userform

Answer 1

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