Réalisation combobox - RechercheV en VBA

Version imprimable

Bonjour à tous,

Je dispose d'un code VBA permettant de remplir des combobox.
Cependant, mon code permet uniquement de rajouter plusieurs fois la même ligne, je me retrouve avec plusieurs lignes identiques car j'effectue un test sur une variable contenant une donnée précise : exemple : "Toaster 1"
Dans ma base à la colonne A, j'ai plusieurs fois "Toaster 1", et il me prend la première ligne, il faudrait que je trouve le moyen d'incrémenter ...

Je ne sais pas si je me fais bien comprendre... voici mon code :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 Private Sub UserForm_Initialize() Application.DisplayAlerts = False Application.ScreenUpdating = False Menu.Label1.Caption = y Sheets("Reglages").Select ' //Combobox 1 Dim l1 As Long 'déclare la variable lf (Ligne de Fin) Dim x As Variant l1 = Range("A65536").End(xlUp).Row 'définit la variable l1 Menu.ComboBox1.Clear 'vide la ComboBox 'remplit la ComboBox For Each cel In Range("A2:A" & l1) 'boucle sur toutes les cellule de A1 à A_lf x = Application.WorksheetFunction.VLookup(cel, Sheets("Reglages").Range("A2:E" & l1), 2, False) 'condition : si la cellule n'est pas vide ajoute son contenu à la ComboBox If cel.Value = y Then Menu.ComboBox1.AddItem x Next cel 'prochaine cellule
Merci d'avance pour votre aide.

GK

Bonjour,

Peut-être en essayant comme ceci ?

Code:

1
2
3
4
5
6
7
8
9
    For Each cel In Range("A2:A" & l1) 'boucle sur toutes les cellule de A1 à A_lf
    'condition : si la cellule = le Label = y, ajoute son contenu à la ComboBox  
    If cel.Value = y Then 
        x = Application.WorksheetFunction.VLookup(cel, Sheets("Reglages").Range("A2:E" & l1), 2, False) 
        Menu.ComboBox1 = x  'affecte la valeur au combobox
        'Si ListIndex = -1, la valeur n'a pas été trouvée et on l'ajoute
        If Menu.ComboBox1.ListIndex = -1 then  Menu.ComboBox1.AddItem x
    End If
    Next cel 'prochaine cellule

--> j'obtiens cette erreur :"La méthode 'Range' de l'objet '_Global' à échoué"

pour information, voici ma base de données sur laquelle pointe ces informations :

Col A		Col B			Col C
Equipements	Menu déroulant 1	Menu déroulant 2
Toaster 1	Maintenance	
Toaster 1	Panne	
Toaster 1	Entretien		Lames
Toaster 1	Entretien		Buses
Toaster 1	Entretien		Filtre
Toaster 1	Entretien		Filtre
Toaster 1	Entretien		Filtre
Toaster 1	Entretien		Détecteur de métaux
Toaster 1	Entretien		Trieur Optique
Toaster 1	Entretien		Trieur Optique
Toaster 1	Entretien		Trieur Optique
Toaster 1	Entretien		Trieur Optique
Toaster 1	Entretien		Trieur Optique

Bonjour,

Si tu veux récupérer les valeur situées en colonne B en face de la valeur cherchée en colonne A, teste avec Find :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
 
Private Sub UserForm_Initialize()
 
    Dim Plage As Range
    Dim Cel As Range
    Dim Adr As String
 
    'y est quoi, Une variable déclarée en tête de module et initialisée ailleurs ?
    'c'est bien la valeur recherchée ?
    Menu.Label1.Caption = y
 
    'vide le ComboBox
    Menu.ComboBox1.Clear
 
    'défini la plage de recherche en colonne A de la feuille "Reglages"
    With Worksheets("Reglages")
 
        Set Plage = .Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
 
    End With
 
    'lance la recherche
    Set Cel = Plage.Find(y, , xlValues, xlWhole)
 
    'si trouvé...
    If Not Cel Is Nothing Then
 
        mémorise l 'adresse pour l'arrêt du bouclage
        Adr = Cel.Adr
 
        Do
 
            'ajoute la valeur située en colonne B dans le ComboBox
            Menu.ComboBox1.AddItem Cel.Offset(, 1).Value
 
            'continu la recherche
            Set Cel = Plage.FindNext(Cel)
 
        's'arrête quand on se retrouve au début
        Loop While Adr <> Cel.Adr
 
    End If
 
End Sub

19/05/2016, 08h56
Ghost0000

La variable y est bien initialisé ailleurs dans mon document, j'ai fais un msgbox, elle porte la valeur "Toaster 1", donc c'est bien la valeur recherchée.

J'ai bien mis en place ton code, lors de l'apparition de l'userform, j'ai une erreur : "Propriété ou méthode non gérée" sur la ligne "Menu.show".

Je vais continuer à chercher, je ne comprend pas d'où vient ce problème.

Je vous tiens au courant.

GK

Citation:

--> j'obtiens cette erreur :"La méthode 'Range' de l'objet '_Global' à échoué"

Sur quelle ligne survient l'erreur ?

Se pourrait-il que x retourne une erreur lors du VLookUp ? Genre #N/A ?
Si oui, il faudrait peut-être vérifier avec IsError(x)
Code:

1 2 3 4 5 6 x = Application.WorksheetFunction.VLookup(cel, Sheets("Reglages").Range("A2:E" & l1), 2, False) If Not IsError(x) then Menu.ComboBox1 = x 'affecte la valeur au combobox 'Si ListIndex = -1, la valeur n'a pas été trouvée et on l'ajoute If Menu.ComboBox1.ListIndex = -1 then Menu.ComboBox1.AddItem x End If

19/05/2016, 11h44
Ghost0000

J'ai corrigé l'erreur : C'étais un mauvais copier-coller de ma part (j'avais omis certaines partie du code, au début que j'avais placer en commentaires)

Il n'y a pas d'erreur à "x", j'ai fais un msgbox de x, je trouve la valeur "Maintenance"

J'ai testé ton code, et dans ma listbox, je trouve uniquement la valeur "Maintenance", sans les autres valeurs, il faudrait que dans mon combobox, j'obtienne :
"Maintenance"
"Panne"
"Entretien"

d'après la liste fournit plus haut.

En fait, dans ma base, il y a plusieurs lignes "Toaster 1" que je teste et mon Vlookup renvoi la première ligne à chaque fois sans regarder les suivantes...

Je continue mes recherches...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
Private Sub UserForm_Initialize()
    Dim I As Long, nbLignes As Long
    Dim y As String
 
    nbLignes = Cells(Rows.Count, "B").End(xlUp).Row
    y = Range("A3") 'ou autre...
    For I = 2 To nbLignes
        If Range("A" & I) = y Then
            If Range("B" & I) <> "" Then
                Me.ComboBox1 = Range("B" & I)
                If Me.ComboBox1.ListIndex = -1 Then Me.ComboBox1.AddItem Range("B" & I)
            End If
 
            If Range("C" & I) <> "" Then
                Me.ComboBox2 = Range("C" & I)
                If Me.ComboBox2.ListIndex = -1 Then Me.ComboBox2.AddItem Range("C" & I)
            End If
        End If
    Next
 
End Sub

Hey !! Ca marche nickel !! Par contre, un truc qu'il me faudrait : Lors de la sélection du 1er combobox, il faudrait que cela conditionne le 2ème, et lors de la sélection du 3eme, que cela conditionne le 4ème (une sorte de filtre selon le contenu de la BDD)

Une idée comment terminé ???

Voilà le code :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Dim I As Long, nbLignes As Long
    nbLignes = Cells(Rows.Count, "B").End(xlUp).Row
 
    For I = 2 To nbLignes
 
        If Range("A" & I) = y Then
 
            If Range("B" & I) <> "" Then
                Me.ComboBox1 = Range("B" & I)
                If Me.ComboBox1.ListIndex = -1 Then Me.ComboBox1.AddItem Range("B" & I)
            End If
 
            If Range("C" & I) <> "" Then
                Me.ComboBox2 = Range("C" & I)
                If Me.ComboBox2.ListIndex = -1 Then Me.ComboBox2.AddItem Range("C" & I)
            End If
 
            If Range("D" & I) <> "" Then
                Me.ComboBox3 = Range("D" & I)
                If Me.ComboBox3.ListIndex = -1 Then Me.ComboBox3.AddItem Range("D" & I)
            End If
 
            If Range("E" & I) <> "" Then
                Me.ComboBox4 = Range("E" & I)
                If Me.ComboBox4.ListIndex = -1 Then Me.ComboBox4.AddItem Range("E" & I)
            End If
 
        End If
    Next

Une façon de faire pourrait être comme ceci
Pour éviter de charger les autres combobox, je mets une variable booléenne.
À chaque fois qu'un item est ajouté à un combobox, l'événement Click est appelé... ce qui n'est pas souhaitable dans ce cas-ci.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
Option Explicit
 
Dim Y
Dim Loading As Boolean
 
Private Sub ComboBox1_Click()
    If Loading Then Exit Sub
    Charger 2
    Me.ComboBox2.ListIndex = -1
End Sub
 
Private Sub ComboBox2_Click()
    If Loading Then Exit Sub
    Charger 3
    Me.ComboBox3.ListIndex = -1
End Sub
 
Private Sub ComboBox3_Click()
    If Loading Then Exit Sub
    Charger 4
    Me.ComboBox4.ListIndex = -1
End Sub
 
Private Sub UserForm_Initialize()
    Y = Range("A2")
 
    Charger 1, Y
End Sub
 
Sub Charger(Idx As Integer, Optional Valeur)
    Dim I As Long, nbLignes As Long
    Dim Colonne As Long
 
    Loading = True      'pour empêcher de charger tous les combobox
    Colonne = Idx + 1   'en tenant compte que Combobox1 = colonne 2, Combox2 = colonne 3...
    nbLignes = Cells(Rows.Count, Colonne).End(xlUp).Row
    Me.Controls("Combobox" & Idx).Clear
 
    For I = 2 To nbLignes
        If Not IsMissing(Valeur) Then
            If Cells(I, Colonne - 1) = Valeur Then
                Me.Controls("Combobox" & Idx) = Cells(I, Colonne)
                If Me.Controls("Combobox" & Idx).ListIndex = -1 Then Me.Controls("Combobox" & Idx).AddItem Cells(I, Colonne)
            End If
        Else
            If CStr(Cells(I, Colonne - 1)) = Me.Controls("Combobox" & Idx - 1).Text Then
                Me.Controls("Combobox" & Idx) = Cells(I, Colonne)
                If Me.Controls("Combobox" & Idx).ListIndex = -1 Then Me.Controls("Combobox" & Idx).AddItem Cells(I, Colonne)
            End If
        End If
    Next
    Loading = False
End Sub

J'ai trouvé la solution avec un peu de recherche -->Merci a tous pour votre code !!! :)

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
 
Private Sub ComboBox1_Click()
Application.ScreenUpdating = False
Sheets("Reglages").Select
 
Dim I As Long, nbLignes As Long, nblignes2 As Long, nblignes3 As Long, nblignes4 As Long
 
    nbLignes = Cells(Rows.Count, "B").End(xlUp).Row
    nblignes2 = Cells(Rows.Count, "C").End(xlUp).Row
    nblignes3 = Cells(Rows.Count, "D").End(xlUp).Row
    nblignes4 = Cells(Rows.Count, "E").End(xlUp).Row
 
'Combobox 2
    For I = 2 To nblignes2 'C
        If Range("B" & I) = ComboBox1.Value And Range("A" & I) = y Then
                If Range("C" & I) <> "" Then
                   Me.ComboBox2 = Range("C" & I)
                   If Me.ComboBox2.ListIndex = -1 Then Me.ComboBox2.AddItem Range("C" & I)
               End If
        End If
     Next
Sheets("Carte").Activate
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
 
 
 
Private Sub ComboBox2_Click()
Application.ScreenUpdating = False
Sheets("Reglages").Select
 
Dim I As Long, nbLignes As Long, nblignes2 As Long, nblignes3 As Long, nblignes4 As Long
 
    nbLignes = Cells(Rows.Count, "B").End(xlUp).Row
    nblignes2 = Cells(Rows.Count, "C").End(xlUp).Row
    nblignes3 = Cells(Rows.Count, "D").End(xlUp).Row
    nblignes4 = Cells(Rows.Count, "E").End(xlUp).Row
 
'Combobox 3
    For I = 2 To nblignes3 ' D
            If Range("C" & I) = ComboBox2.Value And Range("B" & I) = ComboBox1.Value And Range("A" & I) = y Then
               If Range("D" & I) <> "" Then
                   Me.ComboBox3 = Range("D" & I)
                   If Me.ComboBox3.ListIndex = -1 Then Me.ComboBox3.AddItem Range("D" & I)
               End If
            End If
    Next
Sheets("Carte").Activate
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
 
 
 
Private Sub ComboBox3_Click()
Application.ScreenUpdating = False
Sheets("Reglages").Select
 
Dim I As Long, nbLignes As Long, nblignes2 As Long, nblignes3 As Long, nblignes4 As Long
 
    nbLignes = Cells(Rows.Count, "B").End(xlUp).Row
    nblignes2 = Cells(Rows.Count, "C").End(xlUp).Row
    nblignes3 = Cells(Rows.Count, "D").End(xlUp).Row
    nblignes4 = Cells(Rows.Count, "E").End(xlUp).Row
 
'Combobox 4
    For I = 2 To nblignes4 ' E
            If Range("D" & I) = ComboBox3.Value And Range("C" & I) = ComboBox2.Value And Range("B" & I) = ComboBox1.Value And Range("A" & I) = y Then
               If Range("E" & I) <> "" Then
                   Me.ComboBox4 = Range("E" & I)
                   If Me.ComboBox4.ListIndex = -1 Then Me.ComboBox4.AddItem Range("E" & I)
               End If
            End If
    Next
Sheets("Carte").Activate
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

et voici l'initialisation de l'userform :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
 
Private Sub UserForm_Initialize()
Application.DisplayAlerts = False
Application.ScreenUpdating = False
 
Menu.Label1.Caption = y
Menu.Caption = "Menu" & " - " & y
 
Sheets("Reglages").Select
 
Dim I As Long, nbLignes As Long, nblignes2 As Long, nblignes3 As Long, nblignes4 As Long
 
    nbLignes = Cells(Rows.Count, "B").End(xlUp).Row
    nblignes2 = Cells(Rows.Count, "C").End(xlUp).Row
    nblignes3 = Cells(Rows.Count, "D").End(xlUp).Row
    nblignes4 = Cells(Rows.Count, "E").End(xlUp).Row
 
'Combobox 1
    For I = 2 To nbLignes ' B
        If Range("A" & I) = y Then
               If Range("B" & I) <> "" Then
                   Me.ComboBox1 = Range("B" & I)
                   If Me.ComboBox1.ListIndex = -1 Then Me.ComboBox1.AddItem Range("B" & I)
               End If
        End If
    Next
 
' //Combobox 5 
    Dim l5 As Long 'déclare la variable lf (Ligne de Fin)
    l5 = Range("F65536").End(xlUp).Row 'définit la variable lf
    Menu.ComboBox5.Clear 'vide la ComboBox
    'remplit la ComboBox
    For Each Cel In Range("F2:F" & l5) 'boucle sur toutes les cellule de A1 à A_lf
    'condition : si la cellule n'est pas vide ajoute son contenu à la ComboBox
    If Cel.Value <> "" Then Menu.ComboBox5.AddItem Cel.Value
    Next Cel 'prochaine cellule
 
Sheets("Carte").Activate
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub