Discussion :

[yankee_business]

Bonjour,

J'ai un petit souci pour récupérer des valeurs de cellulle dans un label suite à des filtres issus de listes déroulantes faites dans un Userform.

Ci-joint le code qui me permet de filtrer avec des listes déroulantes. Je filtre sur les 7 premières colonnes de ma base de données et j'aimerais afficher dans 4 labels différents les valeurs des 4 colonnes suivantes. Soit une BD à 11 colonnes. J'ai mis un imprime écran de la BD en pièce jointe.

Est ce que quelqu un peut m'aider?

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
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
Private Sub UserForm_Initialize()
  Set f = Sheets("BD")
  Set mondico = CreateObject("Scripting.Dictionary")
  For Each c In Range(f.[A2], f.[A65000].End(xlUp))
    mondico(c.Value) = c.Value
  Next c
  Me.ComboBox1.List = mondico.items
End Sub
Private Sub ComboBox1_Change()
  Set mondico = CreateObject("Scripting.Dictionary")
  For Each c In Range(f.[A2], f.[A65000].End(xlUp))
    If c = Me.ComboBox1 Then mondico(c.Offset(, 1).Value) = c.Offset(, 1).Value
  Next c
  Me.ComboBox2.List = mondico.items
  Me.ComboBox2.ListIndex = -1
  Me.ComboBox3.ListIndex = -1
End Sub
 
Private Sub ComboBox2_Change()
  Set mondico = CreateObject("Scripting.Dictionary")
  For Each c In Range(f.[A2], f.[A65000].End(xlUp))
    If c = Me.ComboBox1 And c.Offset(, 1) = Me.ComboBox2 Then mondico(c.Offset(, 2).Value) = c.Offset(, 2).Value
   Next c
   Me.ComboBox3.List = mondico.items
   Me.ComboBox3.ListIndex = -1
End Sub
 
Private Sub ComboBox3_Change()
  Set mondico = CreateObject("Scripting.Dictionary")
  For Each c In Range(f.[A2], f.[A65000].End(xlUp))
    If c = Me.ComboBox1 And c.Offset(, 1) = Me.ComboBox2 And c.Offset(, 2) = Me.ComboBox3 Then mondico(c.Offset(, 3).Value) = c.Offset(, 3).Value
   Next c
   Me.ComboBox4.List = mondico.items
   Me.ComboBox4.ListIndex = -1
End Sub
Private Sub ComboBox4_Change()
  Set mondico = CreateObject("Scripting.Dictionary")
  For Each c In Range(f.[A2], f.[A65000].End(xlUp))
    If c = Me.ComboBox1 And c.Offset(, 1) = Me.ComboBox2 And c.Offset(, 2) = Me.ComboBox3 And c.Offset(, 3) = Me.ComboBox4 Then mondico(c.Offset(, 4).Value) = c.Offset(, 4).Value
   Next c
   Me.ComboBox5.List = mondico.items
   Me.ComboBox5.ListIndex = -1
End Sub
Private Sub ComboBox5_Change()
  Set mondico = CreateObject("Scripting.Dictionary")
  For Each c In Range(f.[A2], f.[A65000].End(xlUp))
    If c = Me.ComboBox1 And c.Offset(, 1) = Me.ComboBox2 And c.Offset(, 2) = Me.ComboBox3 And c.Offset(, 3) = Me.ComboBox4 And c.Offset(, 4) = Me.ComboBox5 Then mondico(c.Offset(, 5).Value) = c.Offset(, 5).Value
   Next c
   Me.ComboBox6.List = mondico.items
   Me.ComboBox6.ListIndex = -1
End Sub
Private Sub ComboBox6_Change()
  Set mondico = CreateObject("Scripting.Dictionary")
  For Each c In Range(f.[A2], f.[A65000].End(xlUp))
    If c = Me.ComboBox1 And c.Offset(, 1) = Me.ComboBox2 And c.Offset(, 2) = Me.ComboBox3 And c.Offset(, 3) = Me.ComboBox4 And c.Offset(, 4) = Me.ComboBox5 And c.Offset(, 5) = Me.ComboBox6 Then mondico(c.Offset(, 6).Value) = c.Offset(, 6).Value
   Next c
   Me.ComboBox7.List = mondico.items
   Me.ComboBox7.ListIndex = -1
End Sub
Private Sub ComboBox7_Change()
   Label9.Caption =  'Label dans lequel j'aimerais afficher la valeur de la colonne 8 après filtrage...à répéter 3 fois avec les 3 colonnes suivantes
 
End Sub
Private Sub CommandButton1_Click()
  ActiveCell = Me.ComboBox1
  ActiveCell.Offset(, 1) = Me.ComboBox2
  ActiveCell.Offset(, 2) = Me.ComboBox3
  ActiveCell.Offset(, 3) = Me.ComboBox4
  ActiveCell.Offset(, 4) = Me.ComboBox5
  ActiveCell.Offset(, 5) = Me.ComboBox6
  ActiveCell.Offset(, 6) = Me.ComboBox7
  ActiveCell.Offset(, 7) = Me.Label9
  Unload Me
End Sub