bonjour a tous je viens vers vous pour une petite question qui m’embête depuis quelques jours :
j'ai fais un un classeur excel que j'ai nomé BD, contenant des nom, prénom, num. de badge, heures, secteurs, tél,....
j'ai créé dans visual basic un userform1 avec différentes frames et autres fonctions
dont voici le code
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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
Option Explicit
Option Compare Text
Private x As Variant
Private pl As Range
Private cel As Range
Private nl As Long
Private Sub TextBox1_Change()
 
End Sub
 
Private Sub TextBox2_Change()
 
End Sub
 
Private Sub TextBox3_Change()
 
End Sub
 
Private Sub TextBox4_Change()
 
End Sub
 
Private Sub TextBox6_Change()
 
End Sub
 
Private Sub TextBox7_Change()
 
End Sub
 
Private Sub Label1_Click()
 
End Sub
 
Private Sub Label2_Click()
 
End Sub
 
Private Sub Label3_Click()
 
End Sub
 
Private Sub Label4_Click()
 
End Sub
 
Private Sub Label5_Click()
 
End Sub
 
Private Sub Label6_Click()
 
End Sub
 
Private Sub Label7_Click()
 
End Sub
 
 
Private Sub CommandButton1_Click()
' Valider
Dim dest As Range
With Sheets("BD")
    If nl = 0 Then
 
        Set dest = .Cells(Application.Rows.Count, 1).End(xlUp).Offset(1, 0)
    Else
        Set dest = .Cells(nl, 1)
    End If
End With
For x = 1 To 6
     dest.Value = Me.Controls("TextBox1").Value
     dest.Offset(0, x).Value = Me.Controls("TextBox" & x + 1).Value
 Next x
Unload Me
userform1.Show
End Sub
 
Private Sub ComboBox1_Change()
 
  Me.ListBox1.Clear
  For Each cel In pl
    If CStr(cel.Value) = CStr(Me.ComboBox1.Value) Then
      nl = cel.Row
      With Me.ListBox1
        .AddItem Sheets("BD").Cells(cel.Row, 1)
        .List(.ListCount - 1, 1) = Sheets("BD").Cells(nl, 2)
        .List(.ListCount - 1, 2) = nl
      End With
    End If
  Next cel
  If Me.ListBox1.ListCount = 1 Then Me.ListBox1.ListIndex = 0
End Sub
 
Private Sub ListBox1_Click()
 
  nl = Me.ListBox1.Column(2, Me.ListBox1.ListIndex)
  For x = 0 To 7
    Me.Controls("TextBox" & x + 1).Value = Sheets("BD").Cells(nl, 1 + x)
  Next x
 
  With Me.TextBox1
   ' .SetFocus
    .SelStart = 0
    .SelLength = Len(.Value)
  End With
End Sub
 
Private Sub OptionButton2_Click()
ComboBox1.Visible = True
Call obG1
End Sub
 
Private Sub OptionButton3_Click()
ComboBox1.Visible = True
Call obG2
End Sub
 
Private Sub OptionButton4_Click()
ComboBox1.Visible = True
Call obG2
End Sub
 
Private Sub CommandButton2_Click()
Unload Me
End Sub
 
Private Sub OptionButton1_Click()
ComboBox1.Visible = True
Call obG1
End Sub
 
Private Sub obG1()
Dim col As Variant
Dim dico As Object
Dim tbl As Variant
Dim I As Variant
Dim j As Variant
Dim temp As Variant
 
userform1.ComboBox1.Clear
col = IIf(userform1.OptionButton2.Value = True, 7, 1)
With Sheets("BD")
    Set pl = .Range(.Cells(2, col), .Cells(Application.Rows.Count, col).End(xlUp))
End With
 
Set dico = CreateObject("scripting.dictionary")
For Each cel In pl
    dico(cel.Value) = ""
Next cel
tbl = dico.keys
 
 
For I = 0 To UBound(tbl, 1)
For j = 0 To UBound(tbl, 1)
        If tbl(I) < tbl(j) Then
            temp = tbl(I)
            tbl(I) = tbl(j)
            tbl(j) = temp
        End If
    Next j
Next I
userform1.ComboBox1.List = tbl
End Sub
 
Private Sub obG2()
Dim col As Variant
Dim dico As Object
Dim tbl As Variant
Dim I As Variant
Dim j As Variant
Dim temp As Variant
 
userform1.ComboBox1.Clear
col = IIf(userform1.OptionButton4.Value = True, 5, 4)
With Sheets("BD")
    Set pl = .Range(.Cells(2, col), .Cells(Application.Rows.Count, col).End(xlUp))
End With
 
Set dico = CreateObject("scripting.dictionary")
For Each cel In pl
    dico(cel.Value) = ""
Next cel
tbl = dico.keys
 
 
For I = 0 To UBound(tbl, 1)
For j = 0 To UBound(tbl, 1)
        If tbl(I) < tbl(j) Then
            temp = tbl(I)
            tbl(I) = tbl(j)
            tbl(j) = temp
        End If
    Next j
Next I
userform1.ComboBox1.List = tbl
End Sub
je souhaite avoir sur mon userform1, 4 listbox (4 secteur dans l'usine ou je travaille) contenant la liste en temps réel des personnes présentes dans ces différents secteurs, sans que je n'ai besoin de ne rien faire que d'avoir mon userform ouvert(ce qui est le cas toute la journée)
exemple :

nom-------prénom----badge------heures----------secteur---------poste-----------------------------------------------liste box secteur 1-----------------liste box secteur 2--------------liste box secteur 3-------------liste box secteur 4
Edmond----Tony------17141----14h00-16h00--------2-------------3531----------------------------------------------------------------------------------------Edmond Tony
Durand-----Pierre-----12578----14h00-16h00--------4-------------1821------------------------------------------------------------------------------------------------------------------------------------------------------------Durand Pierre
...