View original (French)

Retrieve the line of a value in a combobox.

Solved
remy 42 -  
 remy 42 -
Hello,

Here is my statement, I have a userform in which I have 3 comboboxes and 2 textboxes. An Excel table where I have 5 columns, 2 of which are empty. The purpose of the userform is to fill in the 2 empty columns based on the 3 filled ones. Thus, the 3 comboboxes are cascading, allowing me to reach a specific and unique value in the 3rd column of my table (the first 2 columns can have duplicates). The 2 textboxes in my userform are used to insert data into the 2 cells right next to the value obtained at the end of my 3rd combobox.

I have already managed to code my 3 cascading comboboxes, my issue is therefore inserting the content of the 2 textboxes next to the cell corresponding to the value obtained in the 3rd combobox.

I would like to know how I can obtain the row number of the value obtained in my 3rd combobox.

For example: 
 x = "row number of the value from the 3rd combobox" cells(x,3)= textbox.aaaa


I would like to know what equals x basically.

5 answers

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

    For your ComboBox3, you have defined a range of cells. When you choose a row in your ComboBox3, you can retrieve the index of that choice using the ComboBox3.ListIndex property. Knowing that ListIndex starts at 0, you can easily find the row of your table based on the choice in ComboBox3.

    Have a good continuation.
    1
  2. remy 42
     
    I thank you for responding to me, however I did not really understand how I should write all this. If you could give me a link explaining all this or an example, even mine. Please.
    0
  3. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Hello,
    another way:
    you retrieve the line on your sheet using syntax like this

    the list of values is found in column C starting from row 2; here look for (example) the value of your combo 
     Lig = .Columns("C").Find(What:=cherche, after:=Range("C1"), LookIn:=xlValues).Row cells(lig,3)= textbox.aaaa


    Michel
    0
  4. remy 42
     
    The .columns after the =, it's not too fond of it ^^
    "incorrect or unqualified reference"

    Otherwise, lig is defined as an integer, isn't it?

    Because if I don't put the dot in front of columns, it highlights the line in yellow and tells me "type mismatch"
    0
    1. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
       
      ```html With Sheets("feuil1")
      cherche=combox3.value
      Lig = .Columns("B").Find(What:=cherche, after:=.Range("B2"), LookIn:=xlValues).Row
      Cells(Lig, "C")=textbox.aaa
      T_lig(ordre) = Lig 'mémorise la ligne du mémo en feuil1
      End With

      lig peut être déclaré comme byte, integer ou long suivant la grandeur de ton tableau

      mais tu peux utiliser ce qu'a fait F89 -bonjour :o) - ca marche aussi
      list index te donne la position dans la colonne du combo; il ajouter a listindex le numéro ligne de départ des occurences de ta combo; c'est même d'ailleurs plus simple ```
      0