Thread: [Solved] Excel Combobox List
View Single Post
 
Old 01-06-2016, 02:45 PM
NoSparks NoSparks is offline Windows 7 64bit Office 2010 64bit
Excel Hobbyist
 
Join Date: Nov 2013
Location: British Columbia, Canada
Posts: 842
NoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of lightNoSparks is a glorious beacon of light
Default

I'm not sure I follow what you're doing, and definitely don't follow when it's being done.
Using the _Change() event will fire the FillCombobox procedure every time any character is typed into the combo box.
I'd think you would want the drop downs to populate before entering anything.
Perhaps using the _GotFocus event instead of _Change.
Code:
Private Sub ComboBox1_GotFocus()
    Call FillCombobox("List", "A", Me.ComboBox1)
End Sub
and like this for the FillCombobox procedure
Code:
Sub FillCombobox(WSName As String, ColLtr As String, CBox As ComboBox)
    Dim LastRow As Long
Set ws = ActiveWorkbook.Worksheets("List")
With ws
    LastRow = .Cells(.Rows.Count, ColLtr).End(xlUp).Row
    CBox.List = .Range(ColLtr & "3:" & ColLtr & LastRow).Value
End With
End Sub
Reply With Quote