Listbox - affichage valeur

[Manu62]

Bonjour,
Ci dessous mon code pour une recherche multicritère dans un userform
Ya t'il un beug ?
Pour moi j'ai un prob avec la listbox 7
Quelqu'un peux t'il m'aider

Merci

Manu

Private Sub ComboBox1_Change()
ThisWorkbook.Worksheets("bd").Range("av2").Value = ComboBox1.Text
ThisWorkbook.Worksheets("bd").Range("aw2").Value = ComboBox2.Text
Range("filtre").ClearContents
Range("liste").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"Crit"), CopyToRange:=Range("dest"), Unique:=False
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("ba2"), Range("bb1").End(xlDown))
ListBox1.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bd2"), Range("be1").End(xlDown))
ListBox2.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bj2"), Range("bk1").End(xlDown))
ListBox3.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bk2"), Range("bl1").End(xlDown))
ListBox4.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bl2"), Range("bm1").End(xlDown))
ListBox5.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bm2"), Range("bn1").End(xlDown))
ListBox6.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bo2"), Range("bp1").End(xlDown))
ListBox7.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bp2"), Range("bq1").End(xlDown))
ListBox8.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bq2"), Range("br1").End(xlDown))
ListBox9.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("br2"), Range("bs1").End(xlDown))
ListBox10.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bs2"), Range("bt1").End(xlDown))
ListBox11.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bt2"), Range("bu1").End(xlDown))
ListBox12.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bu2"), Range("bv1").End(xlDown))
ListBox13.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bv2"), Range("bw1").End(xlDown))
ListBox14.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bw2"), Range("bx1").End(xlDown))
ListBox15.RowSource = "filtre"
End Sub
Private Sub ComboBox2_Change()
ThisWorkbook.Worksheets("bd").Range("aw2").Value = ComboBox2.Text
Range("filtre").ClearContents
Range("liste").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"Crit"), CopyToRange:=Range("dest"), Unique:=False
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("ba2"), Range("bb1").End(xlDown))
ListBox1.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bd2"), Range("be1").End(xlDown))
ListBox2.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bj2"), Range("bk1").End(xlDown))
ListBox3.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bk2"), Range("bl1").End(xlDown))
ListBox4.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bl2"), Range("bm1").End(xlDown))
ListBox5.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bm2"), Range("bn1").End(xlDown))
ListBox6.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bo2"), Range("bp1").End(xlDown))
ListBox7.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bp2"), Range("bq1").End(xlDown))
ListBox8.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bq2"), Range("br1").End(xlDown))
ListBox9.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("br2"), Range("bs1").End(xlDown))
ListBox10.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bs2"), Range("bt1").End(xlDown))
ListBox11.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bt2"), Range("bu1").End(xlDown))
ListBox12.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bu2"), Range("bv1").End(xlDown))
ListBox13.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bv2"), Range("bw1").End(xlDown))
ListBox14.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bw2"), Range("bx1").End(xlDown))
ListBox15.RowSource = "filtre"
End Sub
Private Sub ComboBox3_Change()
ThisWorkbook.Worksheets("bd").Range("aX2").Value = ComboBox3.Text
Range("filtre").ClearContents
Range("liste").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"Crit"), CopyToRange:=Range("dest"), Unique:=False
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("ba2"), Range("bb1").End(xlDown))
ListBox1.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bd2"), Range("be1").End(xlDown))
ListBox2.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bj2"), Range("bk1").End(xlDown))
ListBox3.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bk2"), Range("bl1").End(xlDown))
ListBox4.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bl2"), Range("bm1").End(xlDown))
ListBox5.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bm2"), Range("bn1").End(xlDown))
ListBox6.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bo2"), Range("bp1").End(xlDown))
ListBox7.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bp2"), Range("bq1").End(xlDown))
ListBox8.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bq2"), Range("br1").End(xlDown))
ListBox9.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("br2"), Range("bs1").End(xlDown))
ListBox10.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bs2"), Range("bt1").End(xlDown))
ListBox11.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bt2"), Range("bu1").End(xlDown))
ListBox12.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bu2"), Range("bv1").End(xlDown))
ListBox13.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bv2"), Range("bw1").End(xlDown))
ListBox14.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bw2"), Range("bx1").End(xlDown))
ListBox15.RowSource = "filtre"
End Sub
Private Sub ComboBox4_Change()
ThisWorkbook.Worksheets("bd").Range("aY2").Value = ComboBox4.Text
Range("filtre").ClearContents
Range("liste").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range( _
"Crit"), CopyToRange:=Range("dest"), Unique:=False
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("ba2"), Range("bb1").End(xlDown))
ListBox1.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bd2"), Range("be1").End(xlDown))
ListBox2.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bj2"), Range("bk1").End(xlDown))
ListBox3.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bk2"), Range("bl1").End(xlDown))
ListBox4.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bl2"), Range("bm1").End(xlDown))
ListBox5.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bm2"), Range("bn1").End(xlDown))
ListBox6.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bo2"), Range("bp1").End(xlDown))
ListBox7.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bp2"), Range("bq1").End(xlDown))
ListBox8.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bq2"), Range("br1").End(xlDown))
ListBox9.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("br2"), Range("bs1").End(xlDown))
ListBox10.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bs2"), Range("bt1").End(xlDown))
ListBox11.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bt2"), Range("bu1").End(xlDown))
ListBox12.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bu2"), Range("bv1").End(xlDown))
ListBox13.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bv2"), Range("bw1").End(xlDown))
ListBox14.RowSource = "filtre"
ActiveWorkbook.Names.Add Name:="filtre", RefersTo:=Range(Range("bw2"), Range("bx1").End(xlDown))
ListBox15.RowSource = "filtre"
End Sub

 

[tototiti2008]

Re : Listbox - affichage valeur

Bonjour Manu,

Compliment, c'est hyper lisible. . Et serait-il possible de savoir quel est ton

prob avec la listbox 7

ou doit-on essayer de deviner ?

 

[tototiti2008]

Re : Listbox - affichage valeur

n'aurais-tu pas aussi un prob avec les listbox 8 à 15 ?

 

[Manu62]

Re : Listbox - affichage valeur

tototiti2008 bonjour,

Merci de tes commentaires !
Effectivement!

Les valeures ne se mettent pas dans la liste box lors de certain filtre?

Merci
Manu

 

[tototiti2008]

Re : Listbox - affichage valeur

pourquoi donnes-tu le nom filtre à toutes tes plages ? et pourquoi pas filtre1, filtre2, filtre3...
en renommant filtre, tu fais disparaître l'ancien nom filtre...

 

[Manu62]

Re : Listbox - affichage valeur

Bonjour,

En fait dans le filtre, mes critères sont de 4 voir plus plus tard

A mon avis, il ya plus simple, mais bon pour le moment...

Merci
Manu

 

[tototiti2008]

Re : Listbox - affichage valeur

Sans fichier je crains de ne pouvoir t'aider plus...
D'ailleurs, je vais devoir partir.

Bonne chance.

 

[Manu62]

Re : Listbox - affichage valeur

Bonjour,

Dans mon code, il ya ThisWorkbook, peux t'on le remplacer par workbook= feuille1

Ici je pense qu'il prend la feuille selectionner et ce n'est pas forcement celui là.

Merci

Manu

 

[Manu62]

Re : Listbox - affichage valeur

Bonjour,
J'utilise l même principe de code dans tous mes userforms.
Par contre, quand j'utilise un userform qui ne fait pas référence à la feuille active, cela m'efface des données.
Ci joint mon fichier, Si vous voyez un beug, pouvez m'aider

A mon avis vous aller critiquer mais je demarre...

Cijoint.fr - Service gratuit de dépôt de fichiers.

Merci bcp
Manu

 

[Manu62]

Re : Listbox - affichage valeur

Bonjour à tous,

Quelqu'un a pu trouver !

Merci d'avance

Manu