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.
Thanks
Regards,
Adam
Option ExplicitDim oWs As WorksheetDim rData As Range, rCL As RangePrivate 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.ShowAllDataEnd SubPrivate 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 WithEnd SubPrivate Sub TextBox1_Change()''/// enable TextBox when data is entered Me.cmdFind.Enabled = Len(Me.TextBox1.Value) > 0End SubPrivate 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 WithEnd Sub
As soon as click the search button I got this message:
Shums Faruk
Yes you were right. I am also getting error. Wait let me fix it.
Roy Cox
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
Please find attached...edited code as per your requirement. UserForm will display all the columns.