Searchable ComboBox

johnsonk

Board Regular
Joined
Feb 4, 2019
Messages
169
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

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

Some videos you may like

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,619
Office Version
  1. 2007
Platform
  1. Windows
Review this:
 

johnsonk

Board Regular
Joined
Feb 4, 2019
Messages
169
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.

VBA 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

VBA 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
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,051
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,086
Messages
5,628,591
Members
416,326
Latest member
NinaChristal

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top