VBA Excel - How to code a filter for a list box search

[mikeVA]

Hi,

I wanted to make a filter in a text box of a form that returns search results. The search works great. My code for the filter returns a run time error when I make an entry in the text box. Can someone help with a filter code? I want to filter only the fiscal year in column A by entering in the text box, ie FY18. I listed the names of all the elements involved in the code below.


Here are all the elements.

1. VBA Form: "frmGLSearch" (Name of Form);
2. VBA Form TextBox:"EnterGL" (TextBox); 3)
3. VBA Form Button: "Search" (Button);
4. VBA Form TextBox: "Filter" (TextBox);
5. VBA Form ListBox: "GLResult" (ListBox);
6. Worksheet: "General Search" (Tab with Named Range and Dynamic Search);
7. Worksheet: "Data" (Source Data);
8. Worksheet: "General" (Tab with Search Button);
9. Named Range: "GeneralSearch"(Name Range on tab General Search with offset formula)

--VBA Code to Filter with a Text Box on Form--

<code>

Private Sub Filter_Change()

Dim i As Long
Dim arrList As Variant

Me.GLResult.Clear
If Worksheets("General Search").Range("A" & Worksheets("General Search").Rows.Count).End(xlUp).Row > 1 And Trim(Me.Filter.Value) <> vbNullString Then
    arrList = Worksheets("General Search").Range("A1:A" & Worksheets("General Search").Range("A" & Worksheets("General Search").Rows.Count).End(xlUp).Row).Value2
    For i = LBound(arrList) To UBound(arrList)
        If InStr(1, arrList(i, 1), Trim(Me.Filter.Value), vbTextCompare) Then
            Me.GLResult.AddItem arrList(i, 1)
        End If
    Next i
End If
If Me.GLResult.ListCount = 1 Then Me.GLResult.Selected(0) = True

End Sub

</code>


---Additional VBA----
<code>

Option Explicit

Private Sub Search_Click()

Dim RowNum As Long
Dim SearchRow As Long

RowNum = 2
SearchRow = 2

Worksheets("Data").Activate

Do Until Cells(RowNum, 1).Value = ""

If InStr(1, Cells(RowNum, 2).Value, EnterGL.Value, vbTextCompare) > 0 Then
Worksheets("General Search").Cells(SearchRow, 1).Value = Cells(RowNum, 1).Value
Worksheets("General Search").Cells(SearchRow, 2).Value = Cells(RowNum, 2).Value
Worksheets("General Search").Cells(SearchRow, 3).Value = Cells(RowNum, 3).Value
Worksheets("General Search").Cells(SearchRow, 4).Value = Cells(RowNum, 4).Value
Worksheets("General Search").Cells(SearchRow, 5).Value = Cells(RowNum, 5).Value
Worksheets("General Search").Cells(SearchRow, 6).Value = Cells(RowNum, 6).Value
Worksheets("General Search").Cells(SearchRow, 7).Value = Cells(RowNum, 7).Value
SearchRow = SearchRow + 1
End If
RowNum = RowNum + 1
Loop

If SearchRow = 2 Then
MsgBox "GL not found"
Exit Sub
End If

GLResult.RowSource = "GeneralSearch"

End Sub

</code>

 

[dmt32]

hi,
you have not stated what the error is but looking at the two codes

In the Filter_Change change code try changing this line

Me.GLResult.Clear

to this

With Me.GLResult
        .RowSource = ""
        .Clear
    End With

and see if this resolves but if not, let us know the error you are getting

Dave

 

[mikeVA]

Perfect! Thanks! That was the error.

 

[dmt32]

Perfect! Thanks! That was the error.

Glad resolved - thanks for feedback

Dave