View original (French)

Using a ComboBox for searching in VBA

Solved
Usgpa Posted messages 14 Status Member -  
 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 Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
     
    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
  2. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
     
    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
  3. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
     
    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
  4. Usgpa Posted messages 14 Status Member 1
     
    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
  6. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
     
    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
  7. Usgpa Posted messages 14 Status Member 1
     
    the index does not belong to the selection...
    0
  8. Usgpa Posted messages 14 Status Member 1
     
    Pijaku Thank you!!! Your second solution was the right one!!! Thank you so much and have a good day!
    0
  9. 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
  10. 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
  11. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
     
    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
  12. 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 Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
       
      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
  13. 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
  14. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
     
    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
  15. 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
  16. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
     
    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
  17. 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
  18. pijaku Posted messages 13513 Registration date   Status Moderator Last intervention   2 773
     
    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
  19. Usgpa
     
    I had closed it like that but unfortunately, it continues...
    0