Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Filter Listbox in user form
I have a form with three controls ,
lstEmp - Employee Listbox with 3 columns txtFilter - Text Box cmdFilter - Button I have hacked the code from web site examples and the help files. When the form is opened the list is populated with the entire Employee List via UserForm_Initialize. This works Correctly. When I enter a filter value I cannot get the filtered results I want, see cmdFilter_Click. I dont believe I am doing anything too dificult, well it shouldnt be. Have done similar things in MS Access but Excel seems to be a bit more tempermental. I did get the filtered list working but the list contained a blank row for all the rows filtered out. I added the list_row variable to control the list when it is both filtered and not filtered. I cannot get it working now though. If anyone can correct, point out, simplify what I am trying to do I would appreciate it. Gavin << Code below Private Sub cmdFilter_Click() Dim s_filt As String s_filt = Me.txtFilter.Value FillListBox Me.lstEmp, s_filt, 1 End Sub Private Sub UserForm_Initialize() FillListBox Me.lstEmp, "", 0 End Sub Private Sub FillListBox(lb As MSForms.ListBox, filter_text As String, filter_col As String) Dim r As Long, c As Long, s As Long Dim t As Long, x As Long, y As Long Dim b_add As Boolean Dim arr_lst() Dim list_row As Long Dim RecordSetArray As Variant Dim strText As String, strValue As String list_row = 0 RecordSetArray = Range("A3:C1244") s = LBound(RecordSetArray, 1) t = UBound(RecordSetArray, 1) x = LBound(RecordSetArray, 2) y = UBound(RecordSetArray, 2) ReDim arr_lst(t - s, y - x) For r = s + 1 To t b_add = False If filter_text = "" Then b_add = True list_row = r - s Else strValue = RecordSetArray(r, x + filter_col) strText = Left(strValue, Len(filter_text)) If UCase(filter_text) = UCase(strText) Then b_add = True list_row = list_row + 1 End If End If If b_add Then For c = x To y arr_lst(list_row, c - x) = RecordSetArray(r, c) Next c End If Next r If list_row 0 Then ReDim Preserve arr_lst(list_row, y - x) With lb .Clear .ColumnHeads = True .List() = arr_lst .ListIndex = -1 ' no item selected End With Else lb.Clear MsgBox "Nothing returned" 'Set arr_lst = Nothing End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to filter listbox data in a form? | Excel Discussion (Misc queries) | |||
User form to change manual field filter in pivot table | Excel Discussion (Misc queries) | |||
Filter Excel Pivot, based on user entry form | Excel Discussion (Misc queries) | |||
User form with a listbox | Excel Programming | |||
Adding items to a spreadsheet from a user form listbox | Excel Programming |