vba - How can I prevent an Excel Combo Box from displaying duplicate entries and/or spaces?

Question

I have created an Excel sheet (named "users) which has a list of random names in column A. Each cell in this column is linked to a Data Validation cell. When the user types a character into this Data Validation cell and selects the drop down arrow, this character (or word) is passed as an argument to the Excel search function for each name. If 1 is returned as a result of the search, that name is incorporated as part of a dynamic list (column D) which in turn is shown in the Data Validation cell. This can be seen below whereby I have entered the character 'A' and returned a list of names that all start with this character.

I have taken things further by creating a separate sheet (named "master") whereby column A now contains in every cell a Data Validation list. I have employed VBA code so that when a user double-clicks one of these data validation cells, its reference is passed as an argument to the search function employed previously in the sheet "users". Please see below:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim str As String
    Dim cboTemp As OLEObject
    Dim SortCell As Variant

    Dim master As Worksheet
    Dim users As Worksheet


    Set master = ThisWorkbook.Sheets("master")
    Set users = ThisWorkbook.Sheets("users")
    Set cboTemp = master.OLEObjects("DataCombo")

    On Error Resume Next

On Error GoTo errHandler

   'If found Data Validation cell
    If Target.Validation.Type = 3 Then
       'MsgBox Target.Address
        SortCell = "master!" & Target.Address

        'Set Target Address to that of search function in InCell column
        With users
            .Range("B2").Value = "=IF(IFERROR(SEARCH(" & SortCell & ",A2,1), 0)=1,1,0)"
            .Range("B2:B131").FillDown
        End With 
    End If

I have progressed things again, in this case when the user double-clicks on one of these data validation lists in column A of sheet "master", a combo box appears in place with a dropdown list of all random names from the "users" sheet. When the user types into the combo box, the list of names is filtered to reflect the dynamic list created in column D of "users". The full VBA code for this is shown below:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    Dim str As String
    Dim cboTemp As OLEObject
    Dim SortCell As Variant

    Dim master As Worksheet
    Dim users As Worksheet


    Set master = ThisWorkbook.Sheets("master")
    Set users = ThisWorkbook.Sheets("users")
    Set cboTemp = master.OLEObjects("DataCombo")

    On Error Resume Next

On Error GoTo errHandler

    'If found Data Validation cell
    If Target.Validation.Type = 3 Then

        'if the cell contains a data validation list
        Cancel = True
        Application.EnableEvents = False

        'MsgBox Target.Address
        SortCell = "master!" & Target.Address

        'Set Target Address to that of search function in InCell column
        With users
            .Range("B2").Value = "=IF(IFERROR(SEARCH(" & SortCell & ",A2,1), 0)=1,1,0)"
            .Range("B2:B131").FillDown
        End With

        'Get Data Validation Formula
        str = Target.Validation.Formula1
        str = Right(str, Len(str) - 1)

        'MsgBox str

        With cboTemp
          'show the combobox with the list
          .Visible = True
          .Left = Target.Left
          .Top = Target.Top
          .Width = Target.Width + 5
          .Height = Target.Height + 5 
          .ListFillRange = str
          .LinkedCell = Target.Address
        End With

        cboTemp.Activate
        Me.DataCombo.DropDown

    End If

errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub

The problem I am having is that when the user types into the combo box and hence creates the new list of names based on the character(s) typed, the combo box retains the original length of the dynamic list - it does not reduce the size of the list to reflect the reduced number of names returned from the search. In addition, blanks are left and names are duplicated to "make up" this residual length. Please see the image below which illustrates this:

I apologise for the length and detail of my question, but I was wondering how I might be able to prevent the combo box from behaving in this way and to enable it to dynamically reduce its drop-down list size to match the length of the list of names returned?

Any thoughts/suggestions most welcome.

Answer 1

This piece of code will resize the combo box drop down list to match that of the size of the dynamic list created in the "users" sheet:

Private Sub DataCombo_Change()

   'When user types into the combo box, this will resize the dropdown list to match the length of the dynamic list created in "users"
    With Me.DataCombo
          .Visible = True
          .ListFillRange = "Employees"
    End With

End Sub

The above code is executed everytime the user types something into the combo box to search for a name.