Excel Combo Box Update Dropdown Menu?
Is there a way to update the combobox? I have the following VBA code. The dropdown is populated until the If statement is cleared and populated with appropriate items.
At this point, the dropdown shows only one item with a scrollbar. But if I close the dropdown and reopen it, it is 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 is called as a custom type on the combo box. the drop-down list changes from a list to a single line with up / down arrows after the Clear and Repopulate items. but if I close the dropdown and reopen it, all items are listed without up / down arrows. .ListRows value = 8.
I would like the dropdown potion to close and reopen .. or a VBA function to update the dropdown without external buttons or controls.
source to share
Getting a list to ONLY display values that matched the text the user entered so far was a nightmare. Below I wrote what works (but took me for a while!)
Note that the MacthEntry property in the combo box MUST be set to "2 - frmMatchEntryNone" for the code to work. (Other values cause the combo box.value property to retain the text of the first value that matches the user entered, and the code relies on it to retain what they typed.)
Also notice the trick to get around the behavior you observed, i.e. list of combo values not correctly calculated was to use lines of code:
LastActiveCell.Activate
ComboBox_SiteName.Activate
In addition, the code will display all items in the list that have letters entered 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 of your code with this, which should give acceptable functionality (as long as the data source is in alpha order) and it's easy! However, this is not exactly 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
Combined fields can be configured to "filter as custom types" - as long as the data is in alphabetical order.
=============================================== === ====================
Note that in your code, the next two lines cause the ComboBox_SiteName_Change event to start over. I suspect you need to add breakpoints and debug your code again.
.Value = UCase(.Value) .Clear ' Clear all items
Anyway, I hope this is a job.
This will be my first bounty if I receive it, so please let me know if you need more help. (I think it might be worth over 50 points)
Harvey
================================================ =
PART 2:
To respond to your comments, follow these steps:
(see tag # 2 in my code above)
To reference data in a table column, excluding the use of a header: = Table5 [SITE NAME] (This will be automatically generated when you enter a formula if you click and drag the data cells in the column). The code has been changed in agreement.
I have used excel 2013 and 2010 and found the .Activate event works in both. See # 3 for minor changes.
Please copy all the code.
Note that I entered the code to try and stop the flickering with Application.ScreenUpdating, but that had no effect - I don't know why. I've left the code behind so you can do further experiments if you need to.
NOTE: new procedure ComboBox_SiteName_KeyDown
================================================ =
PART 3:
To reply to your comments, follow these steps: This is a combo in shape! - make a change with tag # 4 above.
Harvey
source to share