View original (French)

Using a ComboBox for searching in VBA

Solved
Usgpa -  
Hello,

I am a beginner in VBA Excel and I am submitting a case that will probably seem very simple to you.

Actually, I have a list of documents in Sheet2 with 4 columns of characteristics (A, B, C, and D). I have created a Userform where I have a combobox that allows selecting an item from the list by its characteristic A. I would like to know how to make the corresponding data B, C, and D appear in the three Textboxes of my Userform.

Thank you in advance for the time you will give me.

18 answers

  1.  pijaku Moderator
    Hello,
    In Microsoft Visual Basic, double-click on the combo box, the following code appears: 

    Private Sub ComboBox1_Change() End Sub


    Between these two lines write:

    Private Sub ComboBox1_Change() 
    Dim a As Integer Dim cherche As String cherche = ComboBox1.Value a = Sheets("Feuil1").Cells.Find(What:=cherche, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlNext).Row TextBox1 = Range("A" & a).Offset(0, 1).Value TextBox2 = Range("A" & a).Offset(0, 2).Value TextBox3 = Range("A" & a).Offset(0, 3).Value

    End Sub
    Of course, adapt it well
    --
    Best regards, and ..... Happy New Year, my best wishes for 2010!!!
    -- Every problem has its solution. If there is no solution, where is the problem? --
    3
    1. pijaku Moderator
      try without Sheets(""Sheet2"") like this:
      a = Cells.Find(What:=search, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlNext).Row

      Otherwise, how do you complete your combobox?
      --
      Best regards, and ..... Happy New Year, my best wishes for 2010!!!
      -- Every problem has its solution. If there is no solution, where is the problem? --
      2
      1. pijaku Moderator
        Hello,
        Of course. The code: 
        a = Cells.Find(What:=cherche, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlNext).Row
        returns a number: the row number. Then, you can use it to delete one or more cells: 
        Range("B" & a).Delete

        or 
        Range(Cells(1,a),Cells(6,a)).Delete
        to delete from column A to column F
        --
        Best regards, and ..... Happy New Year, my best wishes for 2010!!!
        -- Every problem has its solution. If there is no solution, then where is the problem? --
        1
        1. Usgpa Author
          Thank you Pijaku. Apparently my VBA is blocking on:
          a = Sheets("Sheet2").Cells.Find(What:=search, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlNext).Row

          It highlights it in yellow and reports a Type "9" error.
          0
          2. pijaku Moderator
            Is there a type "9" error? What is the complete error message?
            --
            Best regards, and ..... Happy New Year, my best wishes for 2010!!!
            -- Every problem has its solution. If there is no solution, where is the problem? --
            0
            1. Usgpa Author
              the index does not belong to the selection...
              0
              1. Usgpa Author
                Pijaku Thank you!!! Your second solution was the right one!!! Thank you so much and have a good day!
                0
                1. Usgpa
                  Hello,

                  I would like to know if it is possible, using a "delete" button in a userform, to delete the found cells without having to delete the entire row.

                  Thank you in advance.
                  0
                  1. Usgpa
                    Thank you again Pijaku. Once again your code works but unfortunately, once the cells are deleted, my first userform bugs because the code (a = Cells.Find(What:=cherche, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlNext).Row) becomes yellow since the value that served as a reference no longer exists. The bug also occurs if I unload it when opening the second userform...

                    Do you have any idea?
                    0
                    1. pijaku Moderator
                      We return to my question from post 5: So how do you complete your combobox?
                      I also don't understand why you don't want to delete the entire row? In database management, you either replace or delete an entire row, but leaving empty cells is absolutely not good for a db...
                      --
                      Best regards, and..... Happy New Year, my best wishes for 2010!!!
                      -- Every problem has a solution. If there is no solution, where is the problem? --
                      0
                      1. Usgpa
                        My combobox takes the first column of my sheet, which is one of the characteristics of the listed objects.

                        Actually, I wanted to avoid deleting the entire row so as not to remove the buttons and statistics that I placed to the right of the table... If that poses a problem, I will place them in another sheet; I’m not yet familiar with VBA... and therefore don’t yet have the useful reflexes :)

                        Thank you.
                        0
                        1. pijaku Moderator
                          Indeed, your sheet containing the data should only contain... the data.
                          If you delete cells, you should have gaps in your combo?
                          --
                          Best regards, and ..... Happy New Year, my best wishes for 2010!!!
                          -- Every problem has its solution. If there is no solution, where is the problem? --
                          0
                      2. Usgpa
                        In fact, not knowing how to program a combo to only contain the non-empty cells of a column, I programmed it to take all the cells from column A. As a result, the disappearing cells are not visible.

                        So I will create a homepage with buttons, etc. This way I can delete the entire row. Can you give me the formula to delete an entire row?
                        0
                        1. pijaku Moderator
                          Simply put, adapted to your case: 
                          a = Cells.Find(What:=search, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlNext).Row Rows(a).Delete

                          --
                          Best regards, and ..... Happy New Year, my best wishes for 2010!!!
                          --
                          Every problem has its solution. If there is no solution, where is the problem? --
                          0
                          1. Usgpa
                            Thank you very much. However, once the line is deleted, there is still an error message highlighting in yellow:

                            a = Feuil2.Cells.Find(What:=cherche, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlNext).Row

                            of the first userform...

                            Error 1004
                            0
                            1. pijaku Moderator
                              yes since you're looking again (I don't know why by the way) for the value "search" that you just removed...
                              --
                              Best regards, and ..... Happy New Year, my best wishes for 2010!!!
                              -- Every problem has its solution. If there is no solution, where is the problem? --
                              0
                              1. Usgpa
                                In fact, I programmed the search to start every time there is a change in ComboBox1 (Private Sub ComboBox1_Change()). The strange thing is that the search starts even when my userform containing the combobox is closed... I thought that would solve the problem but apparently it hasn't...
                                0
                                1. pijaku Moderator
                                  Hi,
                                  how did you close it? If you only did: 
                                  UserForm1.Hide
                                  that’s not enough.
                                  Try: 
                                  Unload UserForm1

                                  It would be best if you posted all your code here so we can see why it's not working. I will have a bit of time tomorrow if you want...
                                  --
                                  Best regards, and ..... Happy New Year, my best wishes for 2010!!!
                                  -- Every problem has its solution. If there is no solution, where is the problem? --
                                  0
                                  1. Usgpa
                                    I had closed it like that but unfortunately, it continues...
                                    0