Filtering ListBox Based On TextBox Values In UserForm

Orpheus600 · Mar 26, 2015

Hello Again Guys,

Another stumper I have come across, I am trying to filter my list box using a TextBox but despite using many solutions previously posted here I cannot get them to work specifically one line of the code keeps producing Error but it is the key to the code I have highlighted the error line. Driver_Works_List is my ListBox, Filter_Depot_TextBox is my TextBox

Code:

Sub Lookup_Depot()
'declare the variables
    Dim rngFind As Range
    Dim strFirstFind As String
    'clear the listbox
    [COLOR="#FF0000"]Driver_Works_Form.Driver_Works_List.Clear[/COLOR] 
    With Sheets("Driver Work Orders").Range("B:B")
        Set rngFind = .Find(Driver_Works_Form.Filter_Depot_TextBox.Text, LookIn:=xlValues, lookat:=xlPart)
        'if value found then set a variable for the address
        If Not rngFind Is Nothing Then
            strFirstFind = rngFind.Address
            'add the values to the listbox
            Do
                If rngFind.Row > 1 Then
                    Driver_Works_List.AddItem rngFind.Value
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 1) = rngFind.Offset(0, 1)
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 2) = rngFind.Offset(0, 2)
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 3) = rngFind.Offset(0, 3)
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 4) = rngFind.Offset(0, 4)
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 5) = rngFind.Offset(0, 5)
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 5) = rngFind.Offset(0, 6)
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 5) = rngFind.Offset(0, 7)
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 5) = rngFind.Offset(0, 8)
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 5) = rngFind.Offset(0, 9)
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 5) = rngFind.Offset(0, 10)
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 5) = rngFind.Offset(0, 11)
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 5) = rngFind.Offset(0, 12)
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 5) = rngFind.Offset(0, 13)
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 5) = rngFind.Offset(0, 14)
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 5) = rngFind.Offset(0, 15)
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 5) = rngFind.Offset(0, 16)
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 5) = rngFind.Offset(0, 17)
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 5) = rngFind.Offset(0, 18)
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 5) = rngFind.Offset(0, 19)
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 5) = rngFind.Offset(0, 20)
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 5) = rngFind.Offset(0, 21)
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 5) = rngFind.Offset(0, 22)
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 5) = rngFind.Offset(0, 23)
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 5) = rngFind.Offset(0, 24)
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 5) = rngFind.Offset(0, 25)
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 5) = rngFind.Offset(0, 26)
                End If
                'find the next address to add
                Set rngFind = .FindNext(rngFind)
            Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
        End If
    End With
End Sub

Worf · Mar 28, 2015

Hello
This worked for me:

Code:

Sub Lookup_Depot()
Dim rngFind As Range, strFirstFind$, i%


Driver_Works_Form.Driver_Works_List.RowSource = ""  ' unlink control from sheet


Driver_Works_Form.Driver_Works_List.Clear
Driver_Works_Form.Driver_Works_List.ColumnCount = 5
    
With Sheets("Driver Work Orders").Range("B:B")
    Set rngFind = .Find(Driver_Works_Form.Filter_Depot_TextBox.Text, LookIn:=xlValues, lookat:=xlPart)
    'if value found then set a variable for the address
    If Not rngFind Is Nothing Then
        strFirstFind = rngFind.Address
        'add the values to the listbox
        Do
            If rngFind.Row > 1 Then
                Driver_Works_List.AddItem rngFind.Value
                Driver_Works_List.List(Driver_Works_List.ListCount - 1, 1) = rngFind.Offset(0, 1)
                Driver_Works_List.List(Driver_Works_List.ListCount - 1, 2) = rngFind.Offset(0, 2)
                Driver_Works_List.List(Driver_Works_List.ListCount - 1, 3) = rngFind.Offset(0, 3)
                Driver_Works_List.List(Driver_Works_List.ListCount - 1, 4) = rngFind.Offset(0, 4)
                For i = 5 To 26
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 5) = rngFind.Offset(0, i)
                Next
            End If
            'find the next address to add
            Set rngFind = .FindNext(rngFind)
        Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
    End If
End With
End Sub

Orpheus600 · Mar 29, 2015

Hello Worf,

Thank you for your reply, I have entered the code you specified which got me past the List Clear error. but now it is giving me an Object not Defined error at the line highlighted below.

Worf said:

Hello
This worked for me:

Code:

Sub Lookup_Depot()
Dim rngFind As Range, strFirstFind$, i%


Driver_Works_Form.Driver_Works_List.RowSource = ""  ' unlink control from sheet


Driver_Works_Form.Driver_Works_List.Clear
Driver_Works_Form.Driver_Works_List.ColumnCount = 5
    
With Sheets("Driver Work Orders").Range("B:B")
    Set rngFind = .Find(Driver_Works_Form.Filter_Depot_TextBox.Text, LookIn:=xlValues, lookat:=xlPart)
    'if value found then set a variable for the address
    If Not rngFind Is Nothing Then
        strFirstFind = rngFind.Address
        'add the values to the listbox
        Do
            If rngFind.Row > 1 Then
                [COLOR="#FF0000"]Driver_Works_List.AddItem rngFind.Value[/COLOR]
                Driver_Works_List.List(Driver_Works_List.ListCount - 1, 1) = rngFind.Offset(0, 1)
                Driver_Works_List.List(Driver_Works_List.ListCount - 1, 2) = rngFind.Offset(0, 2)
                Driver_Works_List.List(Driver_Works_List.ListCount - 1, 3) = rngFind.Offset(0, 3)
                Driver_Works_List.List(Driver_Works_List.ListCount - 1, 4) = rngFind.Offset(0, 4)
                For i = 5 To 26
                    Driver_Works_List.List(Driver_Works_List.ListCount - 1, 5) = rngFind.Offset(0, i)
                Next
            End If
            'find the next address to add
            Set rngFind = .FindNext(rngFind)
        Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
    End If
End With
End Sub

Worf · Mar 29, 2015

Is it a compile or execution error? What is the error number? Is it #1004?

Orpheus600 · Mar 30, 2015

Worf said:
Is it a compile or execution error? What is the error number? Is it #1004?

Its Error 424, Object Required?

Worf · Apr 1, 2015

Please test this:

Code:

' form module / tested with Excel 2013
Sub Lookup_Depot()
Dim rngFind As Range, strFirstFind$, i%, L
Set L = Me.Driver_Works_List
L.RowSource = ""  ' unlink control from sheet
L.Clear
L.ColumnCount = 5
With Sheets("Driver Work Orders").Range("B:B")
    Set rngFind = .Find(Me.Filter_Depot_TextBox.Text, LookIn:=xlValues, lookat:=xlPart)
    'if value found then set a variable for the address
    If Not rngFind Is Nothing Then
        strFirstFind = rngFind.Address
        'add the values to the listbox
        Do
            If rngFind.Row > 1 Then
                L.AddItem rngFind.Value
                L.List(L.ListCount - 1, 1) = rngFind.Offset(0, 1)
                L.List(L.ListCount - 1, 2) = rngFind.Offset(0, 2)
                L.List(L.ListCount - 1, 3) = rngFind.Offset(0, 3)
                L.List(L.ListCount - 1, 4) = rngFind.Offset(0, 4)
                For i = 5 To 26
                    L.List(L.ListCount - 1, 5) = rngFind.Offset(0, i)
                Next
            End If
            'find the next address to add
            Set rngFind = .FindNext(rngFind)
        Loop While Not rngFind Is Nothing And rngFind.Address <> strFirstFind
    End If
End With
End Sub

Orpheus600 · Apr 3, 2015

That Worked Great. Thanks for your help

Worf · Apr 5, 2015

You are welcome

Filtering ListBox Based On TextBox Values In UserForm

Orpheus600

New Member

Excel Facts

Worf

Well-known Member

Orpheus600

New Member

Worf

Well-known Member

Orpheus600

New Member

Worf

Well-known Member

Orpheus600

New Member

Worf

Well-known Member

Similar threads

Forum statistics

Share this page

Filtering ListBox Based On TextBox Values In UserForm

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

New Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock