Searching ListBox as i type in TextBox

Andyw111 · Jun 21st 2018

Hi

I need some help with the code below, it searches the contents of the listbox as I type in the textbox and work fine. However if I miss type and use backspace nothing happens.

Can some one help me out so backspace works in the search?

Code

private Sub TextBox10_Change()
Dim j As Long
Dim testString As String




testString = LCase("*" & TextBox10.text & "*")




With ListBox1
For j = .ListCount - 1 To 0 Step -1
If (Not (LCase(.List(j, 0)) Like testString) And (Not (LCase(.List(j, 1)) Like testString))) _
And (Not (LCase(.List(j, 2)) Like testString) And (Not (LCase(.List(j, 3)) Like testString))) Then
.RemoveItem j
End If
Next j
End With
End Sub

Display More

Many thanks in Advance

AndyW

**pike** · Jun 22nd 2018

Hello AndyW and welcome to the forum,
You can just type into the combobox or

Code

Private Sub ComboBox1_Change()
Dim i As Long
    With Me.ComboBox1
        If Not IsArrow Then .List = Range("Your Range").Value
        If .ListIndex = -1 And Len(TextBox1.Text) Then
            For i = .ListCount - 1 To 0 Step -1
               If Not .List(i) Like "*" & TextBox1.Text & "*" Then .RemoveItem i
            Next i
            .DropDown
        End If
    End With
End Sub


Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    IsArrow = KeyCode = vbKeyUp Or KeyCode = vbKeyDown
    If KeyCode = vbKeyReturn Then Me.ComboBox1.List = Range("AccountsList").Value
End Sub


Private Sub TextBox1_Change()
ComboBox1_Change
End Sub

Display More

Andyw111 · Jun 22nd 2018

Hi Pike,

Thanks for the reply.

The typing in the ListBox doesn't work as it has 10 columns of data, which are all searchable from TextBox1 this all works fine and as I type it filters the ListBox results.

But if I hit backspace it clears the text in the TextBox but doesn't update the ListBox and I have to reset the form.

I really need a code where the backspace will continue updating the results in the ListBox.

Many Thanks

Andy

**pike** · Jun 22nd 2018

Hello,
you will need to start with the complete list each change in the text box. where is the list?

Andyw111 · Jun 23rd 2018

Hi Pike

The list is populated like this:

Code

Private Sub UserForm_Initialize()








ListBox1.ColumnCount = 10
ListBox1.List = Sheets("HERS Data").Range("A2:J" & Sheets("HERS Data").Cells(Rows.Count, 1).End(xlUp).Row).value






End Sub

Display More

Thanks in advance

Andy

**pike** · Jun 24th 2018

Hello,
try,,,

Code

Option Explicit
Private Sub ListBox1_Change()
    Dim i As Long
    With Me.ListBox1
       .List = Sheets("HERS Data").Range("A2:J" & Sheets("HERS Data").Cells(Rows.Count, 1).End(xlUp).Row).Value
        If .ListIndex = -1 And Len(TextBox1.Text) Then
            For i = .ListCount - 1 To 0 Step -1
                If (Not (LCase(.List(j, 0)) Like testString) And (Not (LCase(.List(j, 1)) Like testString))) _
And (Not (LCase(.List(j, 2)) Like testString) And (Not (LCase(.List(j, 3)) Like testString))) Then .RemoveItem i
            Next i
        End If
    End With
End Sub


Private Sub TextBox1_Change()
    ListBox1_Change
End Sub
Private Sub UserForm_Initialize()
    ListBox1.ColumnCount = 10
    ListBox1.List = Sheets("HERS Data").Range("A2:J" & Sheets("HERS Data").Cells(Rows.Count, 1).End(xlUp).Row).Value
End Sub

Display More

**pike** · Jun 24th 2018

this will be closer to the mark

Code

Option Explicit
Private Sub ListBox1_Change()
    Dim j As Long, testString As String
    testString = "*" & TextBox1.Text & "*"
    With Me.ListBox1
        .List = Sheets("HERS Data").Range("A2:J" & Sheets("HERS Data").Cells(Rows.Count, 1).End(xlUp).Row).Value
        If .ListIndex = -1 And Len(TextBox1.Text) Then
            For j = .ListCount - 1 To 0 Step -1
                If (Not (LCase(.List(j, 0)) Like testString) And (Not (LCase(.List(j, 1)) Like testString))) _
                   And (Not (LCase(.List(j, 2)) Like testString) And (Not (LCase(.List(j, 3)) Like testString))) Then .RemoveItem j
            Next j
        End If
    End With
End Sub
Private Sub TextBox1_Change()
    ListBox1_Change
End Sub
Private Sub UserForm_Initialize()
    ListBox1.ColumnCount = 10
    ListBox1.List = Sheets("HERS Data").Range("A2:J" & Sheets("HERS Data").Cells(Rows.Count, 1).End(xlUp).Row).Value
End Sub

Display More

Andyw111 · Jun 24th 2018

Hi Pike,

The search now works perfectly thank you.

But I am now getting a run time error 70 permission denied on the line in red below when I select an item in listbox1

Code

Private Sub ListBox1_Change()
    Dim j As Long, testString As String
    testString = "*" & TextBox10.text & "*"
    With Me.ListBox1
    [COLOR=#FF0000] .List = Sheets("HERS Data").Range("A2:J" & Sheets("HERS Data").Cells(Rows.Count, 1).End(xlUp).Row).value[/COLOR]
        If .ListIndex = -1 And Len(TextBox10.text) Then
            For j = .ListCount - 1 To 0 Step -1
                If (Not (LCase(.List(j, 0)) Like testString) And (Not (LCase(.List(j, 1)) Like testString))) _
                   And (Not (LCase(.List(j, 2)) Like testString) And (Not (LCase(.List(j, 3)) Like testString))) Then .RemoveItem j
            Next j
        End If
    End With
End Sub

Display More

Do you have any ideas?

Andy

**pike** · Jun 24th 2018

Hi
Could be protected sheet or using the same list twice.

**pike** · Jun 25th 2018

Hello,
or remove the listbox_change

Code

Option Explicit
Private Sub TextBox1_Change()
    Dim j As Long, testString As String
    testString = "*" & TextBox1.Text & "*"
    With Me.ListBox1
    .Clear
        .List = Sheets("HERS Data").Range("A2:J" & Sheets("HERS Data").Cells(Rows.Count, 1).End(xlUp).Row).Value
        If .ListIndex = -1 And Len(TextBox1.Text) Then
            For j = .ListCount - 1 To 0 Step -1
                If (Not (LCase(.List(j, 0)) Like testString) And (Not (LCase(.List(j, 1)) Like testString))) _
                   And (Not (LCase(.List(j, 2)) Like testString) And (Not (LCase(.List(j, 3)) Like testString))) Then .RemoveItem j
            Next j
        End If
    End With
End Sub
Private Sub UserForm_Initialize()
    ListBox1.ColumnCount = 10
    ListBox1.List = Sheets("HERS Data").Range("A2:J" & Sheets("HERS Data").Cells(Rows.Count, 1).End(xlUp).Row).Value
End Sub

Display More

Andyw111 · Jun 25th 2018

Hi Pike

You're a star that works perfectly.

Many Thanks.

Andy

Searching ListBox as i type in TextBox

Participate now!

Share