Combobox/listbox List property doesn't work with 1 item

[ashley12]

Hi all,

I'm trying to create a running search bar in my userform. Below is my code. SupplierInfo is the search list. wsSearch is a worksheet where the filtered list will be pasted to.
Everything work until the result list only has 1 item. Then it would give an error message as in the attached image. I tried combobox and list box, both have the same problem. Do you know how to fix it or have better way to achieve the result?

Thank you

Private Sub CBSupName_Change()
    SupplierInfo.Activate
    SupplierInfo.ListObjects(1).DataBodyRange.AutoFilter Field:=1, Criteria1:="=*" & CBSupName.Value & "*", Operator:=xlAnd

    wsSearch.UsedRange.ClearContents
    SupplierInfo.ListObjects(1).DataBodyRange.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    wsSearch.Activate
    ActiveSheet.Range("A1").Select
    wsSearch.Paste
    wsSearch.Activate
    CBSupName.List = Selection.Value
    CBSupName.DropDown
End Sub

 

[Fluff]

How about

   wsSearch.Activate
   If Selection.Rows.Count = 1 Then
      x = Array(Selection.Value)
   Else:
      x = Selection.Value
   End If
   CBSupName.List = x