searchable combo box with a list of sugggestion on a userform

Question

I have a user form and some combo boxes with drop down lists. However one of the combo boxes has almost 1000 items in the drop down and I'd like the user to be able to start typing a word in the combo box and get a list of suggestions to choose from based on the word entered eg if they type 'joh' in the combo box I want to show them a list of all the choices in the drop down that contains 'joh' so they can select the one they want. The combo box with almost 1000 items that needs the functionality above is named 'cboProgrammeName'. I should say that the VBA below already provides this functionality but not entirely. At the moment if I type 'joh' in my combo box, then I have to click the arrow at the right of the combo box to see all the given suggestions based on 'joh'. But what I want is VBA to automatically pop up the list of suggestions WITHOUT me having to click the arrow. Is that possible? My user form has a lot of vba but I think the part that is relevant for this issue is below. Although I can post on here all my VBA code if it helps. Thanks in advance

 Private Sub UserForm_Initialize()


'Add the drop down lists to combo boxes
 Dim cProgrammeName As Range
 Dim cTaskName As Range
 Dim cUserName As Range

Dim ws As Worksheet
Set ws = Worksheets("XXX")


  For Each cProgrammeName In ws.Range("ProgrammeNameList")
  With Me.cboProgrammeName
.AddItem cProgrammeName.Value
.List(.ListCount - 1, 1) = cProgrammeName.Offset(0, 1).Value
 End With
 Next cProgrammeName




  For Each cTaskName In ws.Range("TaskNameList")
 With Me.cboTaskName
.AddItem cTaskName.Value
 End With
 Next cTaskName


For Each cUserName In ws.Range("UserNameList")
With Me.cboUserName
.AddItem cUserName.Value
End With
 Next cUserName

Me.txtDate.Value = "dd/mm/yyyy"
Me.txtComments.Value = "please type text here if required"
Me.cboProgrammeName.SetFocus
Me.cboProgrammeName.Value = "type text to open a list of choices"
Me.cboTaskName.Value = "click the arrow to open a list of choices"
Me.cboUserName.Value = "click the arrow to open a list of choices"

 End Sub

You can use something like this which implements an entry completion feature. The feature is activated via a double-click. I've used a variant of this on a validated list with over 15k entries. — Amorpheuses

Subodh Tiwari sktneer Subodh Tiwari sktneer · Accepted Answer · 2017-05-19T18:46:01

You may try something like this...

Place the following code on UserForm Module. Change the Sheet and Range references if required.

Private Sub cboProgrammeName_Change()
Dim ws As Worksheet
Dim x, dict
Dim i As Long
Dim str As String
Set ws = Sheets("XXX")
x = ws.Range("ProgrammeNameList").Value
Set dict = CreateObject("Scripting.Dictionary")
str = Me.cboProgrammeName.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.cboProgrammeName.List = dict.keys
Else
    Me.cboProgrammeName.List = x
End If
Me.cboProgrammeName.DropDown
End Sub

searchable combo box with a list of sugggestion on a userform

1 Answers