Build Searchable Dropdown Control using ComboBox and ListBox


Steps to achieve this control

Step 1: Insert an Userform
Step 2: Insert TextBox from Toolbar
Step 3: Insert ListBox from Toolbar
Step 6: Write Few names in Sheet1 in column 1
Step 7: Insert a button on a worksheet and write the following code to it.

Private Sub CommandButton1_Click()
    UserForm1.Show
End Sub

Step 4: Change ListBox Heigh to 0 from Properties window.
Step 5: Copy Following Code

Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    
    MsgBox Me.ListBox1.List(Me.ListBox1.ListIndex)
    
End Sub

Private Sub TextBox1_Change()
    
    Dim I As Long, tmp As String, arr() As String
    Dim J As Long, ArrCount As Long, K As Long
    
    With Me.ListBox1
        .Clear
        .Height = 0
    End With
    
    If Me.TextBox1.Value = vbNullString Then Exit Sub
    
    tmp = vbNullString
    ArrCount = 0
    
    'Filling the Array with Values which matches the criteria
    With Sheet1
        J = .Cells(.Rows.Count, 1).End(xlUp).Row
        For I = 1 To J Step 1
            If InStr(1, .Cells(I, 1).Value, Me.TextBox1.Text) > 0 Then
                tmp = tmp & .Cells(I, 1).Value & "|"
            End If
        Next I
    End With
    
    'Filling Listbox
    With Me.ListBox1
        .Top = Me.TextBox1.Height + Me.TextBox1.Top
        If tmp <> vbNullString Then
            tmp = VBA.Left(tmp, Len(tmp) - 1)
            Erase arr
            arr = VBA.Split(tmp, "|")
            ArrCount = UBound(arr)
            For K = 0 To ArrCount Step 1
                .AddItem arr(K)
            Next K
            .Height = 100
        Else
            .AddItem "No Item Found"
            .Height = 18
        End If
    End With
    
    'Code By Kamal Bharakhda
    
End Sub


Comments

Popular posts from this blog

Import XML Data in Excel Using VBA

Need for a Class-based Programming in Excel VBA!