Excel Combo Box Refresh Dropdown?

Question

Is there a way to refresh a combobox? I have the following VBA code. The dropdown is populated, until the If statement where the list is cleared and populated with the matched items.

At this point, the dropdown list only shows a single item with a scroll bar. But If I close the pulldown and reopen, it's fully populated correctly.

Private Sub ComboBox_SiteName_Change()
ComboBox_SiteName.DropDown

Dim v As Variant, i As Long
With Me.ComboBox_SiteName
 .Value = UCase(.Value)
 If .Value <> "" And .ListIndex = -1 Then
   v = Worksheets("Address").Range("Table5[[#All],[SITE NAME]]").Value
   .Clear ' Clear all items
   ' Repopulate with matched items
   For i = LBound(v, 1) To UBound(v, 1)
     If LCase(v(i, 1)) Like "*" & LCase(.Value) & "*" Then
      .AddItem v(i, 1)
     End If
   Next i
  Else
  ' Repopulate with all items
  .List = Worksheets("Address").Range("Table5[[#All],[SITE NAME]]").Value
  End If
 End With
End Sub

The ComboBox_Change function gets called as the user types in the combo box.. the dropdown box turns from a list into a single line with Up/Down arrows after the Clear and Repopulate matched items.. but if I close the dropdown portion and reopen it lists all the items without Up/Down arrows. The .ListRows value = 8 by the way.

I would like a way for the dropdown potion to either close and reopen.. or a VBA function to refresh the dropdown portion, Without external buttons or controls Please

Answer 1

Solved!

https://trumpexcel.com/excel-drop-down-list-with-search-suggestions/

You can do what is in the link with some modifications:

"ListFillRange" in combobox properties should be the last column (the one that is changing). If it is a userform the range will go under "RowSource".

And add this code:

Private Sub ComboBox1_Change()

Sheets("Where the data is").Range("B3") = Me.ComboBox1.Value

End Sub

Answer 2

Try changing the command from Change to DropButtonClick

This refreshes the list on a click of the drop down

Answer 3

Getting the list to ONLY show values that matched the text typed by the user so far, was a nightmare. Below is what I wrote which works (but took me a while!)

Note that the MacthEntry Property of the combo box MUST be set to "2 - frmMatchEntryNone" for the code to work. (Other values cause the combo box .value property store the text of the first value that matches what the user typed, and the code relies on it storing what they typed.)

Also note, the trick to get around the behaviour you observed, ie the combo boxes list of values not being sized correctly, was to use the code lines:

LastActiveCell.Activate
ComboBox_SiteName.Activate

Also, the code will pick up any items on the list that have the letters typed by the user ANYWHERE in their text.

Anyway, here's my code:

Private Sub ComboBox_SiteName_GotFocus()

    ' When it first gets the focus ALWAYS refresh the list
    ' taking into acocunt what has been typed so far by the user
    RePopulateList FilterString:=Me.ComboBox_SiteName.Value

    Me.ComboBox_SiteName.DropDown

End Sub

' #4 Private Sub ComboBox_SiteName_Change()
Private Sub ComboBox_SiteName_Enter()

    Dim LastActiveCell As Range

    On Error GoTo err_Handler

    Set LastActiveCell = ActiveCell

    Application.ScreenUpdating = False

    With Me.ComboBox_SiteName

        If .Value = "" Then
            ' Used cleared the combo
            ' Repopulate will all values
            RePopulateList

            .DropDown

        Else

            ' #4 reducdant
            ' LastActiveCell.Select
            ' .Activate

            ' ===========================================
            ' #4 new code
            ' CheckBox1 is another control on the form
            ' which can receive the focus and loose it without event firing
            CheckBox1.SetFocus

            ' This will trigger the GotFocus event handler
            ' which will do a refresnh of the list
            .SetFocus
            ' ===========================================


        End If

    End With

    Application.ScreenUpdating = True

Exit Sub
err_Handler:
     Application.ScreenUpdating = True
     Err.Raise Err.Number, "", Err.Description
     Exit Sub
     Resume

End Sub


Private Sub RePopulateList(Optional FilterString As String = "")

    Dim i As Long
    Dim ValidValues() As Variant

    ' #2 range now refers to just the data cells
    ValidValues = Worksheets("Address").Range("Table5[SITE NAME]").Value

    With Me.ComboBox_SiteName

        If FilterString = "" Then

            ' All all values
            .List = ValidValues

        Else

            ' #2: .List cannot be set to have no items.
            ' so remove all but one
            .List = Array("Dummy Value")

            ' Only add values that match the FilterString parameter
            For i = LBound(ValidValues, 1) To UBound(ValidValues, 1)

                If LCase(ValidValues(i, 1)) Like "*" & LCase(FilterString) & "*" Then
                  .AddItem ValidValues(i, 1)
                End If

            Next i

           ' #2 add this line to remove the dummy item
           .RemoveItem (0)

        End If

    End With



End Sub

Private Sub ComboBox_SiteName_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Application.ScreenUpdating = False
End Sub

======================================================================

You could: Replace all your code with this which should give acceptable functionality (as long a the data source is in alpha order), and it's easy! However, it doesn't quite do what you wanted.

Private Sub ComboBox_SiteName_GotFocus()

    With Me.ComboBox_SiteName
         .List = Worksheets("Address").Range("Table5[[#All],[SITE NAME]]").Value
    End With

    ComboBox_SiteName.DropDown

End Sub

Combo boxes can be set up to "filter as the user types" - so long as the data is in alphabetical order.

======================================================================

Note that in your code the following two lines cause the ComboBox_SiteName_Change event to start again. I suspect you need to add break points and debug you code more.

.Value = UCase(.Value) 
.Clear ' Clear all items

Anyway, I hope this is job done.

this will be my first bounty if I get it, so please let me know if you need any more help. (I think it may be worth more than 50 points)

Harvey

================================================

PART 2:

To answer you comment issues:

(See the #2 tag in my code above)

To refer to a table column's data, excluding the header use: =Table5[SITE NAME] (This will be autogenerated when entering a formula if you click and drag over the data cells in a column). The code has been altered accordlingly.

I used excel 2013 and 2010 and found that the .Activate event works in both. See #3 for a minor change.

Please recopy all the code.

note that I introduced code to try and stop flickering using Application.ScreenUpdating, but it didn;t have any effect - I don't know why. I've left the code in so you can do further experiments should you need to.

NOTE the new procedure ComboBox_SiteName_KeyDown

================================================

PART 3:

To answer you comment issues: It's a combo on a form ! - so make the change tagged with #4 above.

Harvey