Make ComboBox Searchable

JohnsonK · Oct 10th 2020

Hi, This is my first time posting so hope I am doing this correct. 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.

Code

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

Display More

Code

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

Display More

**royUK** · Oct 10th 2020

That is a default in a combobox.

Attach an example of your workbook with some data in it

JohnsonK · Oct 10th 2020

Hi Roy,

When you say that is a default in the ComboBox I'm not sure what mean.

Regards

**royUK** · Oct 10th 2020

It means that as you type then as you type it tries to find a match

JohnsonK · Oct 10th 2020

Roy,

I am probably being really stupid here but are you saying as I type inside the combobox it will try to find a match.

Regards

**royUK** · Oct 11th 2020

That's right

JohnsonK · Oct 11th 2020

Roy,

When I start to type in the ComboBox after the number is entered it triggers the change event so unable to input all the value.

regards

**royUK** · Oct 11th 2020

What are you using the change event for? It triggers whenever a character is entered, I use the exit event to avoid this.

JohnsonK · Oct 11th 2020

Roy,

Using the Exit event lets me search for the value as I wanted but then it does not populate the textboxes until a mouse click, is there a way around this so they just search for the value and select then the textboxes populate like they did when using the change event.

Code

Private Sub ComboBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

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
TextBox4.Value = .Sheets("ALL").Range("N" & ComboBox3.ListIndex + 7).Value
TextBox5.Value = .Sheets("ALL").Range("T" & ComboBox3.ListIndex + 7).Value
TextBox3.Value = .Sheets("ALL").Range("O" & ComboBox3.ListIndex + 7).Value

Workbooks("DataBase").Close
ComboBox3.Enabled = False

End With

Application.ScreenUpdating = True

End Sub

Display More

**royUK** · Oct 15th 2020

No they are two different events

JohnsonK · Oct 15th 2020

Roy,

Decided to use the EXIT event as you suggested and just changed some of the properties.

Thank you for your help on this.

Regards

Make ComboBox Searchable

Participate now!

Share

Tags