filtre listbox si condition combobox

**Gestionnaire_rh** · 04/06/2011, 00h06

Bonsoir le forum,

Mon problème se résume ds l'alimentation de cette listbox2, elle sera visible par commandbutton et serre a filtrer la listbox1 selon la valeur de la combobox5, normalement l'information est afficher mais sur une seul colonne:
listbox1:
matricule / Nom & prénom/ fonction /.....etc
listbox2:
matricule
nom & prénom
fonction

et voici le code que j'ai mis mais sans succès:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Private Sub CommandButton13_Click()
Dim Donnees As New Collection
Dim Plage As Range
Dim Cell As Range
Dim PlageFiltre As Range
Dim Item As Variant
Dim L As Integer
 
ListBox2.Visible = True
 
On Error Resume Next
For i = 1 To 31
With Sheets(i)
Sheets(i).Range("a4:k4").AutoFilter 4, ComboBox5
L = Sheets(i).Range("d65536").End(xlUp).Row
If L = 5 Then GoTo Zap
Set Plage = Sheets(i).Range("A5:k" & L)
Set PlageFiltre = Plage.SpecialCells(xlCellTypeVisible)
 
For Each Cell In PlageFiltre
Donnees.Add Cell.Text, Cell.Text
Next Cell
For Each Item In Donnees
ListBox2.AddItem Item
Next Item
Exit Sub
Zap:
ListBox2.AddItem Sheets(i).Range("A5:k5")
End With
Next
End Sub

merci d'avance

**mercatog** · 04/06/2011, 00h15

Bonsoir
Comme si on ne se donne pas le temps de faire une petite recherche.
Un sujet similaire datant d’aujourd’hui ICI

Soit avec la proposition développée dans la réponse, soit le lien donnant vers la FAQ.

**Gestionnaire_rh** · 04/06/2011, 00h43

Bonsoir mercatog,

je vous l'assure que j'ai consulter mais toujours pas de solutions pour mon problème, je débute avec le vba et j'aimerai que je puisse modifié le mien parce que je pourrai après le conditionner selon la valeur d'autres combobox et sa va servir à l'utilisateur de vérifié sa saisie des variables.
je crois que la premiere partie est bonne et j'arrive a faire le filtre normalement:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
ListBox2.Visible = True
On Error Resume Next
 
For i = 1 To 31
With Sheets(i)
Sheets(i).Range("a4:k4").AutoFilter 4, ComboBox5
L = Sheets(i).Range("d65536").End(xlUp).Row
If L = 5 Then GoTo Zap
Set Plage = Sheets(i).Range("A5:k" & L)
Set PlageFiltre = Plage.SpecialCells(xlCellTypeVisible)

mais l'affichage normal de ma listbox avec ces 11 colonnes est le plus pénible pour moi.
encore merci pour votre réponse.

**mercatog** · 04/06/2011, 01h13

Une proposition pour la compréhension (Les listbox sont limitées à 10 colonnes)

Le code est lancé sur changement de la valeur de ComboBox5 (évènement Change)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Private Sub ComboBox5_Change()
Dim c As Range
Dim L As Long
Dim i As Byte, k As Byte
 
Application.ScreenUpdating = False
With ListBox2
    .Visible = True
    .ColumnCount = 10
    .Clear
End With
For i = 1 To 31
    With Sheets(i)
        .AutoFilterMode = False
        L = .Cells(.Rows.Count, "D").End(xlUp).Row
        .Range("A4:K" & L).AutoFilter Field:=4, Criteria1:=ComboBox5
        If .Range("A4:A" & L).SpecialCells(xlCellTypeVisible).Count > 1 Then
            For Each c In .Range("A5:A" & L).SpecialCells(xlCellTypeVisible)
                On Error Resume Next
                ListBox2.Value = c.Value
                On Error GoTo 0
                If ListBox2.ListIndex = -1 Then
                    ListBox2.AddItem c.Value
                    For k = 1 To 9
                        ListBox2.List(ListBox2.ListCount - 1, k) = c.Offset(0, k)
                    Next k
                Else
                    ListBox2.ListIndex = -1
                End If
            Next c
        End If
        .AutoFilterMode = False
    End With
Next i
End Sub

Ou bien

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Private Sub ComboBox5_Change()
Dim MonDico As Object
Dim c As Range
Dim L As Long
Dim i As Byte, k As Byte
 
Application.ScreenUpdating = False
With ListBox2
    .Visible = True
    .ColumnCount = 10
    .Clear
End With
Set MonDico = CreateObject("Scripting.Dictionary")
For i = 1 To 31
    With Sheets(i)
        .AutoFilterMode = False
        L = .Cells(.Rows.Count, "D").End(xlUp).Row
        .Range("A4:K" & L).AutoFilter Field:=4, Criteria1:=ComboBox5
        If .Range("A4:A" & L).SpecialCells(xlCellTypeVisible).Count > 1 Then
            For Each c In .Range("A5:A" & L).SpecialCells(xlCellTypeVisible)
                If Not MonDico.Exists(c.Value) Then
                    MonDico.Add c.Value, c.Value
                    ListBox2.AddItem
                    For k = 0 To 9
                        ListBox2.List(ListBox2.ListCount - 1, k) = c.Offset(0, k)
                    Next k
                End If
            Next c
        End If
        .AutoFilterMode = False
    End With
Next i
Set MonDico = Nothing
End Sub

**Gestionnaire_rh** · 05/06/2011, 00h38

Bonsoir mercatog,

je vous remercie pour votre réponse, maintenant ma listbox est renseigner le plus normalement après que j'ai modifié la ligne 17

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

If .Range("A4:A" & L).SpecialCells(xlCellTypeVisible).Count > 1

par:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

If .Range("A5:A" & L).SpecialCells(xlCellTypeVisible).Count > 1

mais il y'a deux trucs que j'ai pas compris et j'ai besoin d'explicite le première

(Les listbox sont limitées à 10 colonnes)

, enfin moi j'avais cru qu'une listbox peut contenir plus, ( ma listbox1 contient 11 colonnes) et voici le code affecter:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
Dim plg1 As Range
Dim derlign As Long, y As Integer, cw
Dim i As Byte
ListBox1.RowSource = ""
i = ComboBox1.List(ComboBox1.ListIndex)
derlign = Sheets(i).Range("a65536").End(xlUp).Row
Set plg1 = Sheets(i).Range("A5:k" & derlign)
With ListBox1
    cw = ""
    .ColumnCount = plg1.Columns.Count
    .RowSource = plg1.Address
    .ColumnHeads = True
       For y = 1 To .ColumnCount
       cw = cw & plg1.Columns(y).Width & ";"
       Next
 .ColumnWidths = cw
 .ListIndex = -1
End With

, et puis le second c'est que moi j'ai fais réference a un commandbutton_click et votre code est affecter à la combobox, alors je me demande si votre modification est indisponsable.
et puis si possible un autre coup de main: j'ai pas su ou rajouter le code pour la largeur et les entete des colonnes.

encore une fois merci beaucoup

Cordialement

**mercatog** · 05/06/2011, 01h31

Bonsoir
Je ne sais pas s'il y a limite dans le nombre de colonnes.
Le code n'aura pas besoin d'un bouton pour le lancer, il suffit de changer le valeur de ComboBox5 pour que ta ListBox2 sera mise à jour (selon le critère)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Private Sub ComboBox5_Change()
Dim MonDico As Object
Dim c As Range
Dim L As Long
Dim i As Byte, k As Byte, Nbc As Byte
 
Application.ScreenUpdating = False
Nbc = 10
With ListBox2
    .Visible = True
    .ColumnCount = Nbc
    .ColumnWidths = "100;90;120;90;70;70;90;120;100;90;140"
    .ColumnHeads = True
    .Clear
End With
Set MonDico = CreateObject("Scripting.Dictionary")
For i = 1 To 31
    With Sheets(i)
        .AutoFilterMode = False
        L = .Cells(.Rows.Count, "D").End(xlUp).Row
        .Range("A4:K" & L).AutoFilter Field:=4, Criteria1:=ComboBox5
        If .Range("A4:A" & L).SpecialCells(xlCellTypeVisible).Count > 1 Then
            For Each c In .Range("A5:A" & L).SpecialCells(xlCellTypeVisible)
                If Not MonDico.Exists(c.Value) Then
                    MonDico.Add c.Value, c.Value
                    ListBox2.AddItem
                    For k = 0 To Nbc - 1
                        ListBox2.List(ListBox2.ListCount - 1, k) = c.Offset(0, k)
                    Next k
                End If
            Next c
        End If
        .AutoFilterMode = False
    End With
Next i
Set MonDico = Nothing
End Sub

**Gestionnaire_rh** · 05/06/2011, 13h34

Bonjour mercatog,

merci pour l'explication, mais vous m'avez fourni le code avec un message d'erreur:

erreur d'éxecution 6 dépassement de capacité

, avec le soulignement de la ligne:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

If .Range("A4:A" & L).SpecialCells(xlCellTypeVisible).Count > 1 Then

j'avais cru que ma modification sur A5 au lieu de A4 été juste, et puis toujours pas d"entête de colonnes ou plus tôt créer avec un encadrement mais vide et toujours pas de onzième colonnes de ce fait disant que la listbox ne support de 10 colonnes, c'est vrai que je ne maîtrise pas autant que vous le vba et c'est je vous l'assure que je vais m'appliquer.

**mercatog** · 05/06/2011, 13h41

Bonjour
Tu peux mettre un extrait de ton fichier (sans données confidentielles)?

**Gestionnaire_rh** · 05/06/2011, 14h39

voila j'ai rattacher le fichier et j"ai supprimer les feuilles de 5 a 31 question de volume, si dans les possibilité voir tous les codes et m'informer des erreurs.
enfin vous pouvez juger de mon travail.

encore merci.

Cordialement

**mercatog** · 05/06/2011, 15h36

Pourquoi tu change le A4 avec le A5 aléatoirement?

J'ai fais quelques modifications:

Le L est calculé par rapport à la colonne A.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
```
L = .Cells(.Rows.Count, "A").End(xlUp).Row
```

J'ai changé l'initialisation de MonDico (à l'intérieur de la boucle pour prendre en considération toutes les données de toutes les feuilles. En effet, à l'extérieur de la boucle, si on a un salarié en feuille 01 qui a été comptabilisé, les jours suivants il n'apparaitra pas) !A toi de voir où mettre cette ligne:
Code : Sélectionner tout - Visualiser dans une fenêtre à part
```
Set MonDico = CreateObject("Scripting.Dictionary")
```

J'ai changé l'appel des feuilles.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
```
With Sheets(Format$(i, "00"))
```

Ici les 2 premières lignes doivent commencer en A4 et la 3ème en A5

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
.Range("A4:K" & L).AutoFilter Field:=4, Criteria1:=ComboBox5
        If .Range("A4:A" & L).SpecialCells(xlCellTypeVisible).Count > 1 Then
            For Each c In .Range("A5:A" & L).SpecialCells(xlCellTypeVisible)

Ici le code modifié et testé sur ton fichier

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
Private Sub ComboBox5_Change()
Dim MonDico As Object
Dim c As Range
Dim L As Long
Dim i As Byte, k As Byte, Nbc As Byte
 
Application.ScreenUpdating = False
Nbc = 10
With ListBox2
    .Visible = True
    .ColumnCount = Nbc
    .ColumnWidths = "46;197;100;40;70;70;72;53;56;65;65;"
    .ColumnHeads = True
    .Clear
End With
For i = 1 To 4
    Set MonDico = CreateObject("Scripting.Dictionary")
    With Sheets(Format$(i, "00"))
        .AutoFilterMode = False
        L = .Cells(.Rows.Count, "A").End(xlUp).Row
        .Range("A4:K" & L).AutoFilter Field:=4, Criteria1:=ComboBox5
        If .Range("A4:A" & L).SpecialCells(xlCellTypeVisible).Count > 1 Then
            For Each c In .Range("A5:A" & L).SpecialCells(xlCellTypeVisible)
                If Not MonDico.Exists(c.Value) Then
                    MonDico.Add c.Value, c.Value
                    ListBox2.AddItem
                    For k = 0 To Nbc - 1
                        ListBox2.List(ListBox2.ListCount - 1, k) = c.Offset(0, k)
                    Next k
                End If
            Next c
        End If
        .AutoFilterMode = False
    End With
Next i
Set MonDico = Nothing
End Sub

PS: je ne peux pas me prononcer sur tout ton fichier, mais il y a des efforts à faire dans la manière de coder.:

Les Select/Activate, les Activemachin
Les ranges sans adressage complet (exemple: Range(X) à la place de Worksheets(Y).Range(X)
Absence ou déclaration des variables:
Code : Sélectionner tout - Visualiser dans une fenêtre à part
```
Dim x, y as integer
```
seul y est integer, x est variant
Code non indenté
Trop de modules
Utilisation du Private Sub Worksheet_Calculate() sans précuation
Private Sub Workbook_Open() à revoir, 3 lignes suffisent avec une boucle sur les feuilles.

Voilà, courage (il t'en faut ces temps ci après le 4 subi)

**Gestionnaire_rh** · 05/06/2011, 15h56

Je vous remercie pour vos remarques intéressantes, je avez raison sur ma modif mais je ne sais pas si vous avez constater je n'ai toujours pas d'entêtes pour mes colonnes.

**mercatog** · 05/06/2011, 16h03

Je crois que l’insertion des entêtes (même avec .ColumnHeads = True) n'est fonctionnelle que si la listbox est alimentée via RowSource. (je peux me tromper)
As-tu testé le code?

PS. Pour les entêtes, tu peux regarder du côté des Listview à la place de la ListBox.

Sinon, tu peux t'amuser a mettre des labels en dessus avec tes entêtes.

**Gestionnaire_rh** · 05/06/2011, 16h44

je vous mercatog pour votre assistance, et je crois que je vais opter pour une listview.

encore merci pour tout.

cordialement

filtre listbox si condition combobox [XL-2007]

Macros et VBA Excel

Discussions similaires

Partager

Partager