Searchable ComboBox

[johnsonk]

Hi, I have a combobox with dropdown list with hundreds of PLU numbers, how would I make the combobox searchable so they only have to type the first few numbers to filter the list.
The

With GetObject("P:\DataBase.xlsm")
ComboBox1.List = .Sheets("ALL").Range("B7:B500").Value

 

[DanteAmor]

Review this:

Userform Combobox Behaviour

I am struggling to find the right setting or combination of settings to reproduce this preferred behaviour of my combobox. - I only want the user to be able to select values from this list, no typing in the value (current setting : Style - frmStyleDropDownList) - I wish for the user to type the...

www.mrexcel.com

 

[johnsonk]

Hi DanteAmor,

I have reviewed the above but cannot get it to work.

 

[johnsonk]

Hi, Can anyone help me with this I have a combobox with dropdown list with hundreds of PLU numbers which is pulled from a closed workbook, I would like to make the combobox searchable so they only have to type the first few numbers to filter the list, once they have chosen the PLU they require it then populates five textboxes with information, at the moment it works fine scrolling through the list but could do with the search to speed things up. Below is the code I have at the moment.

Private Sub UserForm_Initialize()
 Dim SourceWB As Workbook
Dim ListItems As Variant
Dim i As Integer
Application.ScreenUpdating = False
With Me.ComboBox3
.Clear
Set SourceWB = Workbooks.Open("P:\DataBase.xlsm", _
False, True)
ListItems = SourceWB.Worksheets(1).Range("B7", Range("B500").End(xlUp)).Value
SourceWB.Close False
Set SourceWB = Nothing
ListItems = Application.WorksheetFunction.Transpose(ListItems)
For i = 1 To UBound(ListItems)
.AddItem ListItems(i)
Next i
.ListIndex = -1
End With
Application.ScreenUpdating = True
End Sub

Private Sub ComboBox3_Change()
Application.ScreenUpdating = False
With GetObject("P:\DataBase.xlsm")
TextBox1.Value = .Sheets("ALL").Range("A" & ComboBox3.ListIndex + 7).Value
TextBox2.Value = .Sheets("ALL").Range("C" & ComboBox3.ListIndex + 7).Value
TextBox3.Value = .Sheets("ALL").Range("O" & ComboBox3.ListIndex + 7).Value
TextBox4.Value = .Sheets("ALL").Range("N" & ComboBox3.ListIndex + 7).Value
TextBox5.Value = .Sheets("ALL").Range("T" & ComboBox3.ListIndex + 7).Value
Workbooks("DataBase").Close
End With
End Sub

 

[NoSparks]

SourceWB.Worksheets(1) and P:\DataBase.xlsm.Sheets("ALL") are the same sheet, right ?

If you filter the combo list, ListIndex becomes useless as a means of establishing the row to use on the "ALL" sheet.
Do the PLUs uniquely identify the rows ?
If so, use range.Find to get the row to populate the text boxes from.