ComboBox Linking to multiple cells in column

[Davemw429]

Help! I have gotten to a point of partial success (much to the thanks of subscribers to this forum!) but am now stumped. I need to have a single combobox pull values from a very large term list (>40,000), and place selections in a single column in sequential cells (eg, B1, B2, B3, etc..). With the following code (credit to Ingolf and MickG) I am able to get the list to popluate cells in a column. However, I cannot seem to find the proper attributes to control the auto selection of items in list. I do want the autocomplete feature so the user can quickly navigate this huge list, but as soon as i start typing, it starts populating terms into the growing list. I would like to have the user actually select the item. Any ideas would be welcome!

Private Sub Worksheet_Activate()
Dim Rng As Range
With Sheets("Listing")
Set Rng = .Range(.Range("A1"), .Range("A" & Rows.Count).End(xlUp))
ComboBox1.ListFillRange = vbNullString
ComboBox1.List = Rng.Value
ComboBox1.ListIndex = 0
End With
End Sub
Private Sub ComboBox1_Change()
Dim Lst As Long
Lst = Cells(Rows.Count, "B").End(xlUp).Row
Lst = Lst + IIf(Cells(Lst, "B") <> "", 1, 0)
Cells(Lst, "B") = ComboBox1.Value
End Sub

 

[Davemw429]

Thanks to those who have commented previously and gotten me to a point of partial success with the ComboBox. However, I have a critical functionality barrier and really need someone to help me get over the limitation of the code I posted previously. How do I allow the auto-word search/complete function to work (to help navigate a huge items list) WITHOUT inserting every letter match?? How do I tell the Combobox that a user must either CLICK or hit ENTER to actually pull a term off the list from which the combobox refers to? MANY THANKS to anyone who can help.