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 |
