Filter the entries of Combobox

[NikosA]

Hi everyone,

I am really new into VBA envirorement and I want your help.

I have create a User-form with a combo-box which contains 1000 items. As you can imagine is really hard to find an item by scrolling the list of combo-box. Also there are a lot of item starting from the same letter.

What I want to do is to filter the combo-box while I am typing the item's name. For instance when I am typing the letter "a" the combo-box will appears all the entries staring from "a", when a type "ap" will appears all the items starting from "ap" etc.

Any help?

Thanks in advance

[georgiboy]

Hi there welcome to the forum,

You can set this using the properties dialogue box or set it with VBA like below:


Me.ComboBox1.MatchEntry = fmMatchEntryComplete

You would need to place this code inside the userform:


Private Sub UserForm_Initialize()

With Me.ComboBox1
.MatchEntry = fmMatchEntryComplete
.RowSource = Sheet1.Range("A2:A" & Sheet1.Range("A" & Rows.Count).End(xlUp).Row).Address
End With


End Sub

It won't list only matches from the letters you type but it will take you to the first instance in the list.

21515

Hope this helps

[NikosA]

Hi, thanks for the prompt reply

I tried you solution and it works great!! Thanks!!

But, how much complicated is the combo-box to list matches from the typed letters? It's more convenient and less time consuming because I have parts with name i.g

"Armor cable 4x16mm"
"Armor cable 4x25mm"
"Armor cable 4x32mm"
"Armor cable 4x50mm" .

So when I type the word "Armor" the list will be filtered and I will choose the wished cable. It won't need to type the whole name.

Thanks

[georgiboy]

It is possible but not simple.

I will have a look tomorrow unless someone else on this forum has a solution first.

[NikosA]

Thank you very much!!

[georgiboy]

I have had a look, this is what i have come up with:


Dim NmRng As Range
Dim NmVar As Variant

Private Sub UserForm_Initialize()

Set NmRng = Sheet1.Range("A2:A" & Sheet1.Range("A" & Rows.Count).End(xlUp).Row).Cells
NmVar = NmRng

With Me.ComboBox1
.ListRows = 30
.MatchEntry = fmMatchEntryNone
.List = NmVar
End With

End Sub

Private Sub ComboBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim x As Long, y As Long, newNmVar() As String
y = 1

With Me.ComboBox1
.DropDown
On Error GoTo errHand
For x = 1 To UBound(NmVar)
If Left(UCase(NmVar(x, 1)), Len(.Text)) = UCase(.Text) Then
ReDim Preserve newNmVar(y)
newNmVar(y) = NmVar(x, 1)
y = y + 1
End If
Next x
.List = newNmVar()
Exit Sub
End With

errHand:
Me.ComboBox1.Clear
End Sub

21525

Hope this helps

[NikosA]

Thank you very much!! I will come back with a feedback!!

[NikosA]

Georgiboy your solution works more than perfect!!! I don't know how can I thank you!!