Listbox - affichage valeur

Manu62

XLDnaute Impliqué
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
 

Manu62

XLDnaute Impliqué
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

XLDnaute Impliqué
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
 

Discussions similaires

Statistiques des forums

Discussions
312 816
Messages
2 092 361
Membres
105 378
dernier inscrit
y07