Search textbox for listbox

[yinkajewole]

Please, I need a search textbox for this code

Private Sub UserForm_Initialize()   
Dim x As Long, FileNum As Long, TotalFile As String, Lines As Variant
FileNum = FreeFile
Open "C:\Temp\History.txt" For Binary As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] 
TotalFile = Space(LOF(FileNum))
Get [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] , , TotalFile
Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] 
Lines = Split(TotalFile, vbCrLf)
With rList
For x = UBound(Lines) To 0 Step -1
    .AddItem Replace(Lines(x), "|", vbNullChar, , 1)
    .List(.ListCount - 1, 1) = Mid(Lines(x), InStr(1, Lines(x), "|") + 1)
Next
End With
rList.ColumnWidths = "0;200"


End Sub

The listbox ColumnCount = -1

 

[DanteAmor]

Insert a textbox1 and a commandbutton1 in your userform, fill in the textbox and press the button

Put the following code in the form

Private Sub CommandButton1_Click()
    Dim x As Long, FileNum As Long, TotalFile As String, Lines As Variant
    FileNum = FreeFile
    Open "C:\Temp\History.txt" For Binary As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] 
    TotalFile = Space(LOF(FileNum))
    Get [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] , , TotalFile
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] 
    Lines = Split(TotalFile, vbCrLf)
    With rList
        .Clear
        For x = UBound(Lines) To 0 Step -1
            If Lines(x) Like "*" & TextBox1.Value & "*" Then
                .AddItem Replace(Lines(x), "|", vbNullChar, , 1)
                .List(.ListCount - 1, 1) = Mid(Lines(x), InStr(1, Lines(x), "|") + 1)
            End If
        Next
    End With
End Sub

 

[yinkajewole]

can't it be done like as soon as I start typing, it should begin to search and when pressing backspace it should return the list?

 

[DanteAmor]

Yes I could, but you did not specify it in your request, it was not clear, I gladly help you make the change, but you could be very specific in what you need.

 

[Rick Rothstein]

can't it be done like as soon as I start typing, it should begin to search and when pressing backspace it should return the list?

Your ListBox and TextBox are in a UserForm, correct? If so, then I think the following code will do what you want, but note that if you have any existing event procedures that are the same as the ones below, they will have to be removed (or, any additional code integrated into them).

Public PublicLines As Variant

Private Sub UserForm_Initialize()
  Dim x As Long, FileNum As Long, TotalFile As String, Lines As Variant
  FileNum = FreeFile
  Open "C:\Temp\History.txt" For Binary As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] 
    TotalFile = Space(LOF(FileNum))
    Get [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] , , TotalFile
  Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] 
  Lines = Split(TotalFile, vbCrLf)
  ReDim PublicLines(0 To UBound(Lines))
  With rList
    For x = UBound(Lines) To 0 Step -1
      PublicLines(x) = Mid(Lines(x), InStr(Lines(x), "|") + 1)
      .AddItem PublicLines(x)
    Next
  End With
End Sub

Private Sub rList_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  With TextBox1
    If KeyCode = vbKeyLeft Then
      rList.ListIndex = -1
      .SelStart = Len(.Text)
      .SetFocus
    ElseIf KeyCode = vbKeyReturn Then
      rList.ListIndex = (rList.ListIndex + 1) Mod rList.ListCount
    End If
  End With
End Sub

Private Sub TextBox1_Change()
  Dim x As Long
  rList.Clear
  For x = 0 To UBound(PublicLines)
    If InStr(1, PublicLines(x), TextBox1.Text, vbTextCompare) = 1 Then rList.AddItem PublicLines(x)
  Next
End Sub

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  With rList
    If KeyCode = vbKeyReturn Then
      KeyCode = 0
      If .ListCount = 0 Then
        .Selected(0) = True
      ElseIf .ListCount = 1 Then
        .Selected(0) = True
      Else
        .SetFocus
        .Selected(0) = True
        .ListIndex = 0
      End If
    ElseIf (KeyCode = vbKeyDown Or (KeyCode = vbKeyRight And TextBox1.SelStart = Len(TextBox1.Text))) And .ListCount > 0 Then
      .SetFocus
      .Selected(0) = True
      .ListIndex = 0
    End If
  End With
End Sub

NOTES
-----------------------------------------------------
1) Not sure why you set the ListBox up as a two-column ListBox with the first column of zero width, but my code assumes a single column ListBox so make sure to change your ListBox ColumnCount property to 1.

2) You did not tell us the name of your TextBox so I assumed TextBox1 (you will have to change all of the TextBox1 references if my guess was wrong). As for the ListBox name, I used your rList name as that is what your posted code used.

3) All of the above code goes in the UserForm code module.

4) The PublicLines variable must be located outside of any procedures and at the top of the code.

5) As written, all my code does is allow the user to select an item in the ListBox, the assumption being that you will have an "action button" on the UserForm that when clicked, will do something with that selected item.

6) As you type, the ListBox list will shrink to show only those items that start with the text typed in so far. At any point you can either mouse click a showing item to select it or press the Enter Key to drop into the list... you will start with the first showing item... either press the Enter Key again or press the down arrow to advance to the next item in the list (the Enter Key will cycle back to the top of the list if you press it when the list showing item is selected). If you accidentally press the Enter Key or accidentally mouse click an item, you can either click into the TextBox or press the Left Array Key in order to resume typing in the TextBox.

 

[DanteAmor]

can't it be done like as soon as I start typing, it should begin to search and when pressing backspace it should return the list?

Try this.

Change the code for this

Private Sub TextBox1_Change()
    Dim x As Long, FileNum As Long, TotalFile As String, Lines As Variant
    FileNum = FreeFile
    Open "C:\Temp\History.txt" For Binary As [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] 
    TotalFile = Space(LOF(FileNum))
    Get [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] , , TotalFile
    Close [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=FileNum]#FileNum[/URL] 
    Lines = Split(TotalFile, vbCrLf)
    With rList
        .Clear
        For x = UBound(Lines) To 0 Step -1
            If Lines(x) Like "*" & TextBox1.Value & "*" Then
                .AddItem Replace(Lines(x), "|", vbNullChar, , 1)
                .List(.ListCount - 1, 1) = Mid(Lines(x), InStr(1, Lines(x), "|") + 1)
            End If
        Next
    End With
End Sub

 

[yinkajewole]

@DanteAmor i'm sorry, the code work as i wanted but it could not search for lines that have more than a word on them.
for instance, i have
apple
grape
pine apple
so, if i type app, i expect it to bring apple and pine apple, but it only brings apple.

 

[Rick Rothstein]

@DanteAmor i'm sorry, the code work as i wanted but it could not search for lines that have more than a word on them.
for instance, i have
apple
grape
pine apple
so, if i type app, i expect it to bring apple and pine apple, but it only brings apple.

Just wondering... did you try the code I posted yet?

 

[DanteAmor]

@DanteAmor i'm sorry, the code work as i wanted but it could not search for lines that have more than a word on them.
for instance, i have
apple
grape
pine apple
so, if i type app, i expect it to bring apple and pine apple, but it only brings apple.

That just makes my code.
Did you change something in the code?

 

[yinkajewole]

Just wondering... did you try the code I posted yet?

I did not try it because the lines of code seams to be much for me, however, i'll give it a try if that's the best solution.