View original (French)

Search in a listbox from a textbox under condition

Solved
Louloude74 Posted messages 62 Status Member -  
Louloude74 Posted messages 62 Status Member -
Hello everyone,

I have this piece of code that allows me to search from a textbox in a listbox. 

Sub IndexSuivant(Debut) Dim i As Integer Dim intPosition As Integer 'choix de la colonne de recherche If OptionButton1.Value = True Then j = 0 Else j = 1 End If For i = Debut To Me.ListBox1.ListCount - 1 If InStr(LCase(Me.ListBox1.Column(j, i)), LCase(Me.TextBox1.Text)) = 1 Then intPosition = i ListBox1.ListIndex = i Lindex = i + 1 'la prochaine recherche commencera ? i + 1 Exit For End If Next i End Sub


I would like to add a condition based on the value of a checkbox.
This value would determine how the search is performed. If false, the search is done on all suppliers (as it currently is), if true, the search is only done on suppliers whose name contains a "/".

Another alternative that comes to mind as I write these words. As the user types in the textbox, the listbox gets cleared, like a filter, reducing the possible choices.

Thank you in advance for your help and have a good day.

Ludwig

5 answers

  1.
    f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
     
    Hello,

    Where does the variable Debut come from?

    Sheet or UserForm?

    Could you provide an example file because regarding the listbox, according to your code, the search is done on different columns depending on the option button?
    1
  2. f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
     
    Hello,

    I am conducting research on two columns
    In your Excel sheet, how many columns do the suppliers with/without "/" span?
    Since you do not provide how the listbox is filled.
    1
    1. Louloude74 Posted messages 62 Status Member 1
       
      Hello,

      In Excel, the suppliers are in a single column. 

      Private Sub UserForm_Initialize() NomFeuille = ActiveSheet.Name DerLigne = Sheets(NomFeuille).Cells(Rows.Count, 1).End(xlUp).Row DerCol = Sheets(NomFeuille).Cells(1, Columns.Count).End(xlToLeft).Column Me.ListBox1.List = Range(Cells(1, 1), Cells(DerLigne, DerCol)).Value End Sub


      Have a nice Sunday!

      Ludwig
      0
      1. f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717 > Louloude74 Posted messages 62 Status Member
         
        Hello,

        Alright, I'll take a look at it.
        0
  3. f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
     
    Hello,

    File for specific search based on a TextBox for filling ListBox.
    The search filters names containing a "/"

    To adapt for your personal file: https://mon-partage.fr/f/vUbJganP/

    Doesn't get the Ucase because the names must be in uppercase in the cells!
    1
    1. Louloude74 Posted messages 62 Status Member 1
       
      Good evening,

      That's great, it works super well.

      The Ucase allows me to force uppercase input in the textbox.
      Because, normally, the suppliers are always in uppercase in my list. But if there's one in lowercase, I'm in trouble. Ideally, in this case, it would be a case-insensitive search.

      Thanks again for your help. It's awesome!

      Have a good evening.

      Ludwig
      0
      1. f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717 > Louloude74 Posted messages 62 Status Member
         
        Hello,

        without taking case into account.
        It's doable without any problem.
        0
      2. Louloude74 Posted messages 62 Status Member 1 > f894009 Posted messages 17417 Registration date   Status Member Last intervention  
         
        Hello,

        I have no doubt that this must be child's play for you.
        I added application.proper to the additem of the listbox. The uppercase letters made the listbox unreadable.

        Thank you again for everything you have done.

        Have a great day

        Ludwig
        0
      3. f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717 > Louloude74 Posted messages 62 Status Member
         
        Re,

        So no follow-up, is that okay with you?
        0
      4. Louloude74 Posted messages 62 Status Member 1 > f894009 Posted messages 17417 Registration date   Status Member Last intervention  
         
        No problem!!!

        Thank you again!!!
        0
  4. Louloude74 Posted messages 62 Status Member 1
     
    Hello, 

    Private Sub TextBox1_change() TextBox1.Text = UCase(TextBox1.Text) Lindex = 0 IndexSuivant (Lindex) End Sub


    During this event.

    The items in the listbox come from an Excel sheet.
    0
    1. f894009 Posted messages 17417 Registration date   Status Member Last intervention   1 717
       
      Re,

      Absolutely, Thierry.
      But why two columns for the search in the ListBox?
      Where does the variable Debut come from: 
      Sub IndexSuivant(Debut)

      For your information, conduct the search on the Excel sheet rather than the ListBox.
      0
  6. Louloude74 Posted messages 62 Status Member 1
     
    Hello,

    Thank you for your response.

    I am doing a search on two columns because I don’t know how else to do it. ;)

    The code mentioned above is a retrieved code.

    I am using a userform to fill in various fields. I needed to list the suppliers, so I did it through a listbox. I added a textbox to use it as a "search engine" for my suppliers, either by name or by their code. Once the supplier is selected, I increment a second list with my selection or selections.
    The problem with "my" code is that it gives me the first value found and the suppliers are duplicated: generic supplier and supplier/code (our suppliers). I would like to have the ability to search only among suppliers with a "/".

    I hope this time I haven't gone off track.

    Thanks again and have a good day.

    Ludwig
    0