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

Found this very usefull macro created by Roy.

1. I would like copy all the information as row after selecting the member
2. Show all information in the userform before selecting the member
3. If possible multiple search criteria - not only the name of the member - would be great.

Screenshot
Thanks

Regards,

Adam


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

Open in new window

Sample.xlsm
Adam ElsheimerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShumsDistinguished Expert - 2017Commented:
Hi Adam,

Please find attached...edited code as per your requirement. UserForm will display all the columns.
Sample_v1.xlsm
0
Adam ElsheimerAuthor Commented:
Hi Shums, thank you for your fast answer.

Following error and can not search the member:

error
Thanks
0
ShumsDistinguished Expert - 2017Commented:
Surprised, I tested and I haven't got any error.
Let me check again.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Adam ElsheimerAuthor Commented:
As soon as click the search button I got this message:

Unbenannt.JPG
0
ShumsDistinguished Expert - 2017Commented:
Yes you were right. I am also getting error. Wait let me fix it.
0
Roy CoxGroup Finance ManagerCommented:
There's a simple reason why that error is occurring.  I suspect that you are searching on Column 1 of the data so -1 results in 0 and there is no Column zero.

I'm not entirely sure what you want but maybe this example is more suitable. Check it out and post back and I'll help you amend it when I get back from work.
FilterForm--1-.zip
1
Adam ElsheimerAuthor Commented:
It is really excellent. I tested it with 53000 entries.

Sample
Q: I would like copy the filtered value in a new sheet, so that I can build a list of selected values.

It would be great if I can do this.

Thank you very much for your effort.

Regards,

Adam
0
Roy CoxGroup Finance ManagerCommented:
Hi Adan

Looking at this example I realise it's from 2009! I've saved it to a an.xlsm file and added a button to move the filtered data to sheet2.

When I get chance I'll  update the code.
FilterForm.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Adam ElsheimerAuthor Commented:
Hi Roy:

Thank you very much for your effort.  I can use the macro for other purposes. I changed the intial version, it works for me.

Regards,

Adam
0
Roy CoxGroup Finance ManagerCommented:
Pleased to help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.