Excel VBA ComBox Box with AutoComplete Search - Excel VBA Searchable Combox - Code Included

Опубликовано: 12 Сентябрь 2024
на канале: Excel Destination

66k

613

This Excel VBA example explains how to create searchable ComboBox with autocomplete feature.
you can get the relevant items as soon as you type in ComboBox and it autocomplete entries basis on matches.

Code is as follows :

Option Explicit
Private Comb_Arrow As Boolean
Private Sub ComboBox1_Change()
Dim i As Long
If Not Comb_Arrow Then
With Me.ComboBox1
.List = Worksheets("Data").Range("A2", Worksheets("Data").Cells(Rows.Count, "A").End(xlUp)).Value
.ListRows = Application.WorksheetFunction.Min(4, .ListCount)
.DropDown
If Len(.Text) Then
For i = .ListCount - 1 To 0 Step -1
If InStr(1, .List(i), .Text, vbTextCompare) = 0 Then .RemoveItem i
Next
.DropDown
End If
End With
End If
End Sub
Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Comb_Arrow = (KeyCode = vbKeyUp) Or (KeyCode = vbKeyDown)
If KeyCode = vbKeyReturn Then Me.ComboBox1.List = Worksheets("Data").Range _
("A2", Worksheets("Data").Cells(Rows.Count, "A").End(xlUp)).Value
End Sub