Adam Elsheimer
asked on
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.
Thanks
Regards,
Adam
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 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
Sample.xlsm
ASKER
Surprised, I tested and I haven't got any error.
Let me check again.
Let me check again.
Yes you were right. I am also getting error. Wait let me fix it.
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
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
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
Pleased to help
Please find attached...edited code as per your requirement. UserForm will display all the columns.
Sample_v1.xlsm