Create an Excel User-form to Display Filter Results - Modify Macro

Option Explicit
Dim oWs As Worksheet
Dim rData As Range, rCL As Range

Private Sub cmdFind_Click()
    Dim strFind As String    'what to find


    If Me.TextBox1 = Empty Then
        MsgBox "Please enter a search term", vbCritical, "Input required"
        Exit Sub
    End If

    strFind = Me.TextBox1.Value

    With oWs
        If Not .AutoFilterMode Then .Range("B2").AutoFilter
        rData.AutoFilter Field:=2, Criteria1:="=*" & strFind & "*" _
                       , Operator:=xlAnd


        Me.MbrList.Clear
        For Each rCL In rData.Columns(2).SpecialCells(xlCellTypeVisible)
            With Me.MbrList
                If rCL.Row > 1 Then
                    .AddItem rCL.Offset(, -1).Value
                    .List(.ListCount - 1, 1) = rCL.Value
                End If
            End With
        Next rCL
    End With
    oWs.ShowAllData
End Sub


Private Sub MbrList_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim lRw As Long
    ''/// only run if filter has been applied
    If Me.TextBox1.Value = Empty Then Exit Sub
    With Sheet1
        lRw = .Cells(.Rows.Count, 1).End(xlUp).Offset(1).Row
        .Cells(lRw, 1).Value = Me.MbrList.Value
        ''//// optional: adds member name to column B
        '.Cells(lRw, 2).Value = Me.MbrList.List(Me.MbrList.ListIndex - 1, 1)
    End With
End Sub

Private Sub TextBox1_Change()
''/// enable TextBox when data is entered
    Me.cmdFind.Enabled = Len(Me.TextBox1.Value) > 0
End Sub

Private Sub UserForm_Initialize()
    Dim iX As Integer
    Dim CW As String
    Set oWs = Sheets("All Members")
    Set rData = oWs.Range("a1").CurrentRegion.Offset(1)
    ''/// disable find button, will be enabled when TextBox 1 is completed
    Me.cmdFind.Enabled = False
    With Me.MbrList
        .ColumnCount = 2
        .List = rData.Value
        'loop through each column & determine it's width, store it in a String
        CW = " "
        For iX = 1 To 3
            CW = CW & rData.Columns(iX).Width & ";"
        Next iX
        CW = CW
        'use the String to define the Column Widths of the listBox
        .ColumnWidths = CW & 0
    End With
End Sub

Select allOpen in new window