drop down list combo box excel

Question

I have a searchable combo box with suggestions to select from as I type letters in it (vba below). However i want to be able to click the arrow of the combo box (if i don't type anything else in it of course) and see the entire drop down list. For some reason the code below does not show me the entire list if I click the arrow.

Any suggestions much appreciated.

    Dim ws As Worksheet
    Dim x, dict
    Dim i As Long
    Dim str As String
    Set ws = Sheets("Lists")
    x = ws.Range("Listing").value
    Set dict = CreateObject("Scripting.Dictionary")
    str = Me.cbo1.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.cbo1.List = dict.keys
   Else
    Me.cbo1.List = x
   End If
   Me.cbo1.DropDown

A.S.H A.S.H · Accepted Answer · 2017-06-05T01:11:31

What you want is that if the arrow is clicked when nothing is yet selected or written in the combo by the user, to show all the items of the list, which you load from the named range "Listing" without any filtering.

To do so, add this event handler to your userform's code:

Private Sub Cbo1_DropButtonClick()
  'If Len(Trim(cbo1.text)) = 0 Then
  If Trim(cbo1.text) = "type here" Then
    cbo1.List = Sheets("Lists").Range("Listing").Value
    Exit Sub
  End If

  ' Your code to add only items that match the characters typed by the user
  ' ...
End Sub

drop down list combo box excel

1 Answers