我有一个下拉列表，之前使用下面的代码填充了工作簿中第二个工作表的值：

Private Sub UserForm_Initialize()

Dim cProd As Range
Dim ws As Worksheet
Dim i As Long

Set ws = ThisWorkbook.Worksheets("DO NOT DELETE")

For Each cProd In ws.Range("ProdList")
    With Me.dropProd
        .AddItem cProd.Value
    End With
Next cProd

Me.dropProd.SetFocus

End Sub

然后，我添加了我找到的代码here，以向其添加可搜索功能，它运行得很好。
然后，我必须调整代码，添加第二个下拉列表，该下拉列表依赖于我之前拥有的第一个下拉列表。为此，我删除了“请勿删除”工作表，并创建了两个集合来存储下拉列表的值。
现在，我的第一个下拉列表将在以下代码中填充：

Sub UpdateAll()

Dim ProdID As String
Dim Prod As String
Dim TF As Boolean
Dim lRow As Long
Dim i, t, s
    
dropProd.Clear
dropPromo.Clear
   
Set ws = ThisWorkbook.Worksheets("Table View")
   
Set cProd = New Collection
    
lRow = ws.Cells(Rows.Count, 1).End(-4162).Row
    
For i = 13 To lRow
    
    ProdID = ws.Cells(i, 2).Value
    Prod = ws.Cells(i, 3).Value

    If ProdID <> "" Then
            
        TF = False
        If cProd.Count <> 0 Then
            For t = 1 To cProd.Count
                If cProd(t) = ProdID & " - " & Prod Then TF = True
            Next
        End If

        If TF = False Then cProd.Add (ProdID & " - " & Prod)
    End If
    Next

For s = 1 To cProd.Count
        dropProd.AddItem (cProd(s))
Next

End Sub

Private Sub UserForm_Initialize()

    Me.dropProd.SetFocus
    UpdateAll 

End Sub

这部分也做得很好，下面是我有麻烦的地方：

Private Sub dropProd_Change()

    Dim ProdInfo As String
    Dim Promo As String
    Dim q, p

    dropPromo.Clear

    lRow = ws.Cells(Rows.Count, 1).End(-4162).Row
    
    If dropProd.Value <> "" Then
        ProdInfo = Mid(dropProd.Value, 1, InStr(1, dropProd.Value, " - ") - 1)
    End If

    'Populates Promo ComboBox
    For q = 13 To lRow

        Promo = ws.Cells(q, 9).Value
        
        If ws.Cells(q, 2).Value = ProdInfo Then dropPromo.AddItem Promo

    Next

End Sub

如果我只是从下拉列表中选择值，上面的代码就可以正常工作，但是每次我尝试搜索任何内容时，它就会中断，问题就出在ProdInfo = Mid(dropProd.Value, 1, InStr(1, dropProd.Value, " - ") - 1)这一行
我试着用另一种方式重写它，但它仍然给我一个错误。另外，我试着合并上面链接中的代码，看看它是否工作，但我不知道在me.dropProd.List = ????上引用什么。我试着让它等于我的集合，当然它不工作，现在我陷入了如何修复它。