Combobox filtering
 

I have a combobox with a large number of entries representing supplier invoice numbers.

I would like to filter the dropdown list as data is entered into the text box. For example, if the user enters 23 in the text box then only invoices starting with with 23 would show up in the dropdown.

I thought of clearing & repopulating the combobox for each digit as the textbox is entered but I cant work out how to return just the entered digits (without the complete first matched record). In any case, there may be a much simpler and more effective solution.

Hope this makes sense. Running Excel 2010.

Thanks in advance.

Combobox filtering
 

Hi Neil,

Am Mon, 10 Mar 2014 17:45:57 -0700 (PDT) schrieb :

I have a combobox with a large number of entries representing supplier invoice numbers.

I would like to filter the dropdown list as data is entered into the text box. For example, if the user enters 23 in the text box then only invoices starting with with 23 would show up in the dropdown.

if you start the userform fill the combobox with all values (in my
example the values are in Sheet1 Range("Z1:Z50")
If a number is entered into the textbox, the values in the combobox will
be filtered.

Private Sub UserForm_Initialize()
Dim myArr As Variant
Dim i As Long

myArr = Sheets("Sheet1").Range("Z1:Z50")
If Me.TextBox1.Value = "" Then
For i = LBound(myArr) To UBound(myArr)
Me.ComboBox1.AddItem myArr(i, 1)
Next
End If
End Sub

Private Sub TextBox1_Change()
Dim myArr As Variant
Dim i As Long

myArr = Sheets("Sheet1").Range("Z1:Z50")

With UserForm1
If Not IsNumeric(.TextBox1.Value) Then
MsgBox "Please enter a number"
Exit Sub
End If
If CLng(.TextBox1.Value) < UBound(myArr) Then
.ComboBox1.Clear
For i = CLng(.TextBox1.Value) - 1 To UBound(myArr)
.ComboBox1.AddItem myArr(i, 1)
Next
.ComboBox1.ListIndex = 0
End If
End With
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Combobox filtering
 

Hi Neil,

Am Tue, 11 Mar 2014 08:17:05 +0100 schrieb Claus Busch:

if you start the userform fill the combobox with all values (in my
example the values are in Sheet1 Range("Z1:Z50")

a little bit easier:

Private Sub TextBox1_Change()
Dim myArr As Variant
Dim i As Long

myArr = Sheets("Sheet1").Range("Z1:Z50")

With UserForm1
If Not IsNumeric(.TextBox1.Value) Then
MsgBox "Please enter a number"
Exit Sub
End If
If CLng(.TextBox1.Value) < UBound(myArr) Then
.ComboBox1.RowSource = ""
For i = CLng(.TextBox1.Value) To UBound(myArr)
.ComboBox1.AddItem myArr(i, 1)
Next
.ComboBox1.ListIndex = 0
End If
End With
End Sub

Private Sub UserForm_Initialize()
If Me.TextBox1.Value = "" Then
Me.ComboBox1.RowSource = "Sheet1!Z1:Z50"
End If
End Sub


Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2