Transformer plusieurs sub en module de classe

**Garlim** · 13/12/2016, 11h47

Bonjour,

J'ai énormément de sub qui se ressemble à une valeur près.
Je voudrais savoir s'il est possible de transformer tout cela en un module de classe et une fonction?

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
Private Sub FOTextBox1_Change()
    If UserForm1.APLabel211.Caption = "décol" Then Exit Sub
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Range("A1:AE" & Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row & "").AutoFilter Field:=1, Criteria1:="*" & UserForm1.FOLabel36.Caption & "*", Operator:=xlFilterValues
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Cells(Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row, 4).Value = FOTextBox1.Text    'Poste niv 0
End Sub
 
Private Sub FOTextBox2_Change()
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Range("A1:AE" & Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row & "").AutoFilter Field:=1, Criteria1:="*" & UserForm1.FOLabel36.Caption & "*", Operator:=xlFilterValues
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Cells(Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row, 5).Value = FOTextBox2.Text    'Nom niv 0
End Sub
 
Private Sub FOTextBox3_Change()
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Range("A1:AE" & Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row & "").AutoFilter Field:=1, Criteria1:="*" & UserForm1.FOLabel36.Caption & "*", Operator:=xlFilterValues
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Cells(Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row, 6).Value = "'" & FOTextBox3.Text    'Tel niv 0
End Sub
 
Private Sub FOTextBox4_Change()
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Range("A1:AE" & Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row & "").AutoFilter Field:=1, Criteria1:="*" & UserForm1.FOLabel36.Caption & "*", Operator:=xlFilterValues
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Cells(Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row, 7).Value = FOTextBox4.Text    'email niv 0
End Sub
 
Private Sub FOTextBox5_Change()
    If UserForm1.APLabel211.Caption = "décol" Then Exit Sub
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Range("A1:AE" & Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row & "").AutoFilter Field:=1, Criteria1:="*" & UserForm1.FOLabel36.Caption & "*", Operator:=xlFilterValues
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Cells(Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row, 8).Value = FOTextBox5.Text    'Poste niv 1
End Sub
 
Private Sub FOTextBox6_Change()
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Range("A1:AE" & Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row & "").AutoFilter Field:=1, Criteria1:="*" & UserForm1.FOLabel36.Caption & "*", Operator:=xlFilterValues
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Cells(Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row, 9).Value = FOTextBox6.Text    'Nom niv 1
End Sub
 
Private Sub FOTextBox7_Change()
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Range("A1:AE" & Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row & "").AutoFilter Field:=1, Criteria1:="*" & UserForm1.FOLabel36.Caption & "*", Operator:=xlFilterValues
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Cells(Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row, 10).Value = "'" & FOTextBox7.Text    'Tel niv 1
End Sub
 
Private Sub FOTextBox8_Change()
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Range("A1:AE" & Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row & "").AutoFilter Field:=1, Criteria1:="*" & UserForm1.FOLabel36.Caption & "*", Operator:=xlFilterValues
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Cells(Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row, 11).Value = FOTextBox8.Text    'email niv 1
End Sub
 
Private Sub FOTextBox9_Change()
    If UserForm1.APLabel211.Caption = "décol" Then Exit Sub
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Range("A1:AE" & Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row & "").AutoFilter Field:=1, Criteria1:="*" & UserForm1.FOLabel36.Caption & "*", Operator:=xlFilterValues
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Cells(Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row, 12).Value = FOTextBox9.Text    'Poste niv 2
End Sub
 
Private Sub FOTextBox10_Change()
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Range("A1:AE" & Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row & "").AutoFilter Field:=1, Criteria1:="*" & UserForm1.FOLabel36.Caption & "*", Operator:=xlFilterValues
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Cells(Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row, 13).Value = FOTextBox10.Text    'Nom niv 2
End Sub
 
Private Sub FOTextBox11_Change()
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Range("A1:AE" & Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row & "").AutoFilter Field:=1, Criteria1:="*" & UserForm1.FOLabel36.Caption & "*", Operator:=xlFilterValues
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Cells(Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row, 14).Value = "'" & FOTextBox11.Text    'Tel niv 2
End Sub
 
Private Sub FOTextBox12_Change()
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Range("A1:AE" & Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row & "").AutoFilter Field:=1, Criteria1:="*" & UserForm1.FOLabel36.Caption & "*", Operator:=xlFilterValues
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Cells(Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row, 15).Value = FOTextBox12.Text    'email niv 2
End Sub
 
Private Sub FOTextBox13_Change()
    If UserForm1.APLabel211.Caption = "décol" Then Exit Sub
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Range("A1:AE" & Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row & "").AutoFilter Field:=1, Criteria1:="*" & UserForm1.FOLabel36.Caption & "*", Operator:=xlFilterValues
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Cells(Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row, 16).Value = FOTextBox13.Text    'Poste niv 3
End Sub
 
Private Sub FOTextBox14_Change()
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Range("A1:AE" & Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row & "").AutoFilter Field:=1, Criteria1:="*" & UserForm1.FOLabel36.Caption & "*", Operator:=xlFilterValues
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Cells(Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row, 17).Value = FOTextBox14.Text    'Nom niv 3
End Sub
 
Private Sub FOTextBox15_Change()
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Range("A1:AE" & Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row & "").AutoFilter Field:=1, Criteria1:="*" & UserForm1.FOLabel36.Caption & "*", Operator:=xlFilterValues
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Cells(Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row, 18).Value = "'" & FOTextBox15.Text    'Tel niv 3
End Sub
 
Private Sub FOTextBox16_Change()
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Range("A1:AE" & Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row & "").AutoFilter Field:=1, Criteria1:="*" & UserForm1.FOLabel36.Caption & "*", Operator:=xlFilterValues
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Cells(Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row, 19).Value = FOTextBox16.Text    'email niv 3
End Sub
 
Private Sub FOTextBox17_Change()
    If UserForm1.APLabel211.Caption = "décol" Then Exit Sub
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Range("A1:AE" & Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row & "").AutoFilter Field:=1, Criteria1:="*" & UserForm1.FOLabel36.Caption & "*", Operator:=xlFilterValues
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Cells(Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row, 20).Value = FOTextBox17.Text    'Poste niv 4
End Sub
 
Private Sub FOTextBox18_Change()
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Range("A1:AE" & Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row & "").AutoFilter Field:=1, Criteria1:="*" & UserForm1.FOLabel36.Caption & "*", Operator:=xlFilterValues
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Cells(Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row, 21).Value = FOTextBox18.Text    'Nom niv 4
End Sub
 
Private Sub FOTextBox19_Change()
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Range("A1:AE" & Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row & "").AutoFilter Field:=1, Criteria1:="*" & UserForm1.FOLabel36.Caption & "*", Operator:=xlFilterValues
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Cells(Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row, 22).Value = "'" & FOTextBox19.Text    'Tel niv 4
End Sub
 
Private Sub FOTextBox20_Change()
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Range("A1:AE" & Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row & "").AutoFilter Field:=1, Criteria1:="*" & UserForm1.FOLabel36.Caption & "*", Operator:=xlFilterValues
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Cells(Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row, 23).Value = FOTextBox20.Text    'email niv 4
End Sub
 
Private Sub FOTextBox21_Change()
    If UserForm1.APLabel211.Caption = "décol" Then Exit Sub
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Range("A1:AE" & Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row & "").AutoFilter Field:=1, Criteria1:="*" & UserForm1.FOLabel36.Caption & "*", Operator:=xlFilterValues
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Cells(Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row, 24).Value = FOTextBox21.Text    'Poste niv 5
End Sub
 
Private Sub FOTextBox22_Change()
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Range("A1:AE" & Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row & "").AutoFilter Field:=1, Criteria1:="*" & UserForm1.FOLabel36.Caption & "*", Operator:=xlFilterValues
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Cells(Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row, 25).Value = FOTextBox22.Text    'Nom niv 5
End Sub
 
Private Sub FOTextBox23_Change()
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Range("A1:AE" & Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row & "").AutoFilter Field:=1, Criteria1:="*" & UserForm1.FOLabel36.Caption & "*", Operator:=xlFilterValues
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Cells(Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row, 26).Value = "'" & FOTextBox23.Text    'Tel niv 5
End Sub
 
Private Sub FOTextBox24_Change()
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Range("A1:AE" & Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row & "").AutoFilter Field:=1, Criteria1:="*" & UserForm1.FOLabel36.Caption & "*", Operator:=xlFilterValues
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Cells(Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row, 27).Value = FOTextBox24.Text    'email niv 5
End Sub
 
Private Sub FOTextBox25_Change()
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Range("A1:AE" & Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row & "").AutoFilter Field:=1, Criteria1:="*" & UserForm1.FOLabel36.Caption & "*", Operator:=xlFilterValues
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Cells(Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row, 29).Value = FOTextBox25.Text    'parma atlas
End Sub

J'ai également ce problème sur des Frames (je cherche à ce qu'au survol de la souris de n'importe quelle frame, le label1 devienne vide) :

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
Private Sub APFRame1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Label1.Caption = ""
End Sub
 
Private Sub APFRame2_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Label1.Caption = ""
End Sub
 
Private Sub APFRame3_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Label1.Caption = ""
End Sub
 
Private Sub APFRame4_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Label1.Caption = ""
End Sub
 
Private Sub APFRame5_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Label1.Caption = ""
End Sub
 
Private Sub APFRame6_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Label1.Caption = ""
End Sub
 
Private Sub APFRame7_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Label1.Caption = ""
End Sub
 
Private Sub APFRame8_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Label1.Caption = ""
End Sub
 
Private Sub APFRame9_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Label1.Caption = ""
End Sub
 
Private Sub APFRame10_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Label1.Caption = ""
End Sub
 
Private Sub APFRame11_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Label1.Caption = ""
End Sub
 
Private Sub APFRame12_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Label1.Caption = ""
End Sub
 
Private Sub APFRame13_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Label1.Caption = ""
End Sub
 
Private Sub APFRame14_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Label1.Caption = ""
End Sub
 
Private Sub APFRame15_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Label1.Caption = ""
End Sub
 
Private Sub APFRame16_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Label1.Caption = ""
End Sub
 
Private Sub APFRame17_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Label1.Caption = ""
End Sub
 
Private Sub APFRame18_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Label1.Caption = ""
End Sub
 
Private Sub APFRame19_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Label1.Caption = ""
End Sub
 
Private Sub APFRame20_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Label1.Caption = ""
End Sub
 
Private Sub APFRame21_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Label1.Caption = ""
End Sub
 
Private Sub APFRame22_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Label1.Caption = ""
End Sub
 
Private Sub APFRame23_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Label1.Caption = ""
End Sub
 
Private Sub APFRame24_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Label1.Caption = ""
End Sub
 
Private Sub APFRame25_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Label1.Caption = ""

Pourriez-vous m'aider à réduire mon code sachant que le Userform pèse 131,5Kb et qu'il faut le passer sous les 64 Kb pour éviter les plantages à l'ouverture?

J'ai cherché sur d'autres sur pas mal de forum, de sites, mais je n'ai jamais réussir à obtenir une solution pour mes 2 cas.
Merci par avance.

Garlim

Invité · 13/12/2016, 14h09

Bonjour,

Code Classe1 :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Public WithEvents txt As MSForms.TextBox
Private Sub txt_Change()
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Range("A1:AE" & Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row & "").AutoFilter Field:=1, Criteria1:="*" & UserForm1.FOLabel36.Caption & "*", Operator:=xlFilterValues
    Workbooks(UserForm1.AcLabel8.Caption).Sheets("Fournisseur").Cells(Sheets("Fournisseur").Cells(65536, 1).End(xlUp).Row, 9).Value = txt.Text    'Nom niv 1
End Sub

Code UserForm1 :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Dim cn(4) As New Classe1
Private Sub UserForm_Initialize()
For i = 0 To 3
Set cn(i).txt = Me.Controls("TextBox" & (i + 1))
Next
End Sub

**Garlim** · 14/12/2016, 16h31

Merci pour ta réponse dysorthographie.
par contre, ça me fait une erreur système lorsque je lance ma userform que je n'avais jamais vu avant:
Nom : erreur.jpg
Affichages : 235
Taille : 22,6 Ko

Nom : erreur.jpg
Affichages : 235
Taille : 22,6 Ko

De plus, comment faire pour que la colonne avance en fonction du numéro de textbox?
exemple pour la textbox1

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Row, 4).Value = FOTextBox1.Text

pour la textbox2 :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

Row, 5).Value = FOTextBox2.Text

Merci pour ta patience.

**Garlim** · 23/12/2016, 15h14

Personne? C'est impossible à faire? Ou encore une fois, est-ce que je m'exprime mal?

ça doit bien être possible de résumer tout ça avec un petit truc dans le userform et un module de classe, non?

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
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
Private Sub PATextBox1_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(4, 2) = PATextBox1.Text
End Sub
 
Private Sub PATextBox2_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(5, 2) = PATextBox2.Text
End Sub
 
Private Sub PATextBox3_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(6, 2) = PATextBox3.Text
End Sub
 
Private Sub PATextBox4_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(7, 2) = PATextBox4.Text
End Sub
 
Private Sub PATextBox5_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(8, 2) = PATextBox5.Text
End Sub
 
Private Sub PATextBox6_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(9, 2) = PATextBox6.Text
End Sub
 
Private Sub PATextBox7_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(10, 2) = PATextBox7.Text
End Sub
 
Private Sub PATextBox8_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(11, 2) = PATextBox8.Text
End Sub
 
Private Sub PATextBox9_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(12, 2) = PATextBox9.Text
End Sub
 
Private Sub PATextBox10_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(13, 2) = PATextBox10.Text
End Sub
 
Private Sub PATextBox11_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(14, 2) = PATextBox11.Text
End Sub
 
Private Sub PATextBox12_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(15, 2) = PATextBox12.Text
End Sub
 
Private Sub PATextBox13_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(16, 2) = PATextBox13.Text
End Sub
 
Private Sub PATextBox14_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(17, 2) = PATextBox14.Text
End Sub
 
Private Sub PATextBox15_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(18, 2) = PATextBox15.Text
End Sub
 
Private Sub PATextBox16_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(19, 2) = PATextBox16.Text
End Sub
 
Private Sub PATextBox17_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(20, 2) = PATextBox17.Text
End Sub
 
Private Sub PATextBox18_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(21, 2) = PATextBox18.Text
End Sub
 
Private Sub PATextBox19_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(22, 2) = PATextBox19.Text
End Sub
 
Private Sub PATextBox20_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(23, 2) = PATextBox20.Text
End Sub
 
Private Sub PATextBox21_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(4, 6) = PATextBox21.Text
End Sub
 
Private Sub PATextBox22_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(5, 6) = PATextBox22.Text
End Sub
 
Private Sub PATextBox23_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(6, 6) = PATextBox23.Text
End Sub
 
Private Sub PATextBox24_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(7, 6) = PATextBox24.Text
End Sub
 
Private Sub PATextBox25_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(8, 6) = PATextBox25.Text
End Sub
 
Private Sub PATextBox26_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(9, 6) = PATextBox26.Text
End Sub
 
Private Sub PATextBox27_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(10, 6) = PATextBox27.Text
End Sub
 
Private Sub PATextBox28_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(11, 6) = PATextBox28.Text
End Sub
 
Private Sub PATextBox29_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(12, 6) = PATextBox29.Text
End Sub
 
Private Sub PATextBox30_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(13, 6) = PATextBox30.Text
End Sub
 
Private Sub PATextBox31_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(14, 6) = PATextBox31.Text
End Sub
 
Private Sub PATextBox32_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(15, 6) = PATextBox32.Text
End Sub
 
Private Sub PATextBox33_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(16, 6) = PATextBox33.Text
End Sub
 
Private Sub PATextBox34_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(17, 6) = PATextBox34.Text
End Sub
 
Private Sub PATextBox35_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(18, 6) = PATextBox35.Text
End Sub
 
Private Sub PATextBox36_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(19, 6) = PATextBox36.Text
End Sub
 
Private Sub PATextBox37_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(20, 6) = PATextBox37.Text
End Sub
 
Private Sub PATextBox38_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(21, 6) = PATextBox38.Text
End Sub
 
Private Sub PATextBox39_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(22, 6) = PATextBox39.Text
End Sub
 
Private Sub PATextBox40_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(23, 6) = PATextBox40.Text
End Sub
 
Private Sub PATextBox99_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(5, 8) = PATextBox99.Text
End Sub
 
Private Sub PATextBox100_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(6, 8) = PATextBox100.Text
End Sub
 
Private Sub PATextBox101_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(7, 8) = PATextBox101.Text
End Sub
 
Private Sub PATextBox102_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(8, 8) = PATextBox102.Text
End Sub
 
Private Sub PATextBox103_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(9, 8) = PATextBox103.Text
End Sub
 
Private Sub PATextBox104_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(10, 8) = PATextBox104.Text
End Sub
 
Private Sub PATextBox105_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(11, 8) = PATextBox105.Text
End Sub
 
Private Sub PATextBox106_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(12, 8) = PATextBox106.Text
End Sub
 
Private Sub PATextBox110_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(5, 10) = PATextBox110.Text
End Sub
 
Private Sub PATextBox111_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(6, 10) = PATextBox111.Text
End Sub
 
Private Sub PATextBox112_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(7, 10) = PATextBox112.Text
End Sub
 
Private Sub PATextBox113_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(8, 10) = PATextBox113.Text
End Sub
 
Private Sub PATextBox114_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(9, 10) = PATextBox114.Text
End Sub
 
Private Sub PATextBox115_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(10, 10) = PATextBox115.Text
End Sub
 
Private Sub PATextBox116_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(11, 10) = PATextBox116.Text
End Sub
 
Private Sub PATextBox117_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(12, 10) = PATextBox117.Text
End Sub
 
'*******************
'Colonne paramètre 2
'*******************
Private Sub PATextBox121_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(5, 11) = PATextBox121.Text
End Sub
 
Private Sub PATextBox122_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(6, 11) = PATextBox122.Text
End Sub
 
Private Sub PATextBox123_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(7, 11) = PATextBox123.Text
End Sub
 
Private Sub PATextBox124_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(8, 11) = PATextBox124.Text
End Sub
 
Private Sub PATextBox125_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(9, 11) = PATextBox125.Text
End Sub
 
Private Sub PATextBox126_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(10, 11) = PATextBox126.Text
End Sub
 
Private Sub PATextBox127_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(11, 11) = PATextBox127.Text
End Sub
 
Private Sub PATextBox128_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(12, 11) = PATextBox128.Text
End Sub
 
'*******************
'Colonne paramètre 3
'*******************
Private Sub PATextBox132_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(5, 12) = PATextBox132.Text
End Sub
 
Private Sub PATextBox133_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(6, 12) = PATextBox133.Text
End Sub
 
Private Sub PATextBox134_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(7, 12) = PATextBox134.Text
End Sub
 
Private Sub PATextBox135_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(8, 12) = PATextBox135.Text
End Sub
 
Private Sub PATextBox136_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(9, 12) = PATextBox136.Text
End Sub
 
Private Sub PATextBox137_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(10, 12) = PATextBox137.Text
End Sub
 
Private Sub PATextBox138_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(11, 12) = PATextBox138.Text
End Sub
 
Private Sub PATextBox139_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(12, 12) = PATextBox139.Text
End Sub
 
'*******************
'Colonne paramètre 4
'*******************
Private Sub PATextBox143_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(5, 13) = PATextBox143.Text
End Sub
 
Private Sub PATextBox144_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(6, 13) = PATextBox144.Text
End Sub
 
Private Sub PATextBox145_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(7, 13) = PATextBox145.Text
End Sub
 
Private Sub PATextBox146_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(8, 13) = PATextBox146.Text
End Sub
 
Private Sub PATextBox147_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(9, 13) = PATextBox147.Text
End Sub
 
Private Sub PATextBox148_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(10, 13) = PATextBox148.Text
End Sub
 
Private Sub PATextBox149_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(11, 13) = PATextBox149.Text
End Sub
 
Private Sub PATextBox150_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(12, 13) = PATextBox150.Text
End Sub
 
'*******************
'Colonne paramètre 5
'*******************
Private Sub PATextBox154_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(5, 14) = PATextBox154.Text
End Sub
 
Private Sub PATextBox155_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(6, 14) = PATextBox155.Text
End Sub
 
Private Sub PATextBox156_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(7, 14) = PATextBox156.Text
End Sub
 
Private Sub PATextBox157_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(8, 14) = PATextBox157.Text
End Sub
 
Private Sub PATextBox158_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(9, 14) = PATextBox158.Text
End Sub
 
Private Sub PATextBox159_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(10, 14) = PATextBox159.Text
End Sub
 
Private Sub PATextBox160_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(11, 14) = PATextBox160.Text
End Sub
 
Private Sub PATextBox161_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(12, 14) = PATextBox161.Text
End Sub
 
'*******************
'Colonne paramètre 6
'*******************
Private Sub PATextBox165_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(5, 15) = PATextBox165.Text
End Sub
 
Private Sub PATextBox166_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(6, 15) = PATextBox166.Text
End Sub
 
Private Sub PATextBox167_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(7, 15) = PATextBox167.Text
End Sub
 
Private Sub PATextBox168_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(8, 15) = PATextBox168.Text
End Sub
 
Private Sub PATextBox169_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(9, 15) = PATextBox169.Text
End Sub
 
Private Sub PATextBox170_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(10, 15) = PATextBox170.Text
End Sub
 
Private Sub PATextBox171_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(11, 15) = PATextBox171.Text
End Sub
 
Private Sub PATextBox172_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(12, 15) = PATextBox172.Text
End Sub
 
'*******************
'Colonne paramètre 7
'*******************
Private Sub PATextBox176_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(5, 16) = PATextBox176.Text
End Sub
 
Private Sub PATextBox177_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(6, 16) = PATextBox177.Text
End Sub
 
Private Sub PATextBox178_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(7, 16) = PATextBox178.Text
End Sub
 
Private Sub PATextBox179_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(8, 16) = PATextBox179.Text
End Sub
 
Private Sub PATextBox180_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(9, 16) = PATextBox180.Text
End Sub
 
Private Sub PATextBox181_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(10, 16) = PATextBox181.Text
End Sub
 
Private Sub PATextBox182_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(11, 16) = PATextBox182.Text
End Sub
 
Private Sub PATextBox183_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(12, 16) = PATextBox183.Text
End Sub
 
'*******************
'Colonne paramètre 8
'*******************
Private Sub PATextBox187_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(5, 17) = PATextBox187.Text
End Sub
 
Private Sub PATextBox188_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(6, 17) = PATextBox188.Text
End Sub
 
Private Sub PATextBox189_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(7, 17) = PATextBox189.Text
End Sub
 
Private Sub PATextBox190_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(8, 17) = PATextBox190.Text
End Sub
 
Private Sub PATextBox191_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(9, 17) = PATextBox191.Text
End Sub
 
Private Sub PATextBox192_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(10, 17) = PATextBox192.Text
End Sub
 
Private Sub PATextBox193_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(11, 17) = PATextBox193.Text
End Sub
 
Private Sub PATextBox194_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(12, 17) = PATextBox194.Text
End Sub
 
'*****************************
'Colonne CAGES 1 Signification
'*****************************
Private Sub PATextBox59_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(27, 4) = PATextBox59.Text
End Sub
 
Private Sub PATextBox60_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(28, 4) = PATextBox60.Text
End Sub
 
Private Sub PATextBox61_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(29, 4) = PATextBox61.Text
End Sub
 
Private Sub PATextBox62_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(30, 4) = PATextBox62.Text
End Sub
 
Private Sub PATextBox63_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(31, 4) = PATextBox63.Text
End Sub
 
Private Sub PATextBox64_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(32, 4) = PATextBox64.Text
End Sub
 
Private Sub PATextBox65_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(33, 4) = PATextBox65.Text
End Sub
 
Private Sub PATextBox66_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(34, 4) = PATextBox66.Text
End Sub
 
Private Sub PATextBox67_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(35, 4) = PATextBox67.Text
End Sub
 
Private Sub PATextBox68_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(36, 4) = PATextBox68.Text
End Sub
 
'**********************
'Colonne CAGES 1 Images
'**********************
Private Sub PATextBox69_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(27, 5) = PATextBox69.Text
End Sub
 
Private Sub PATextBox70_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(28, 5) = PATextBox70.Text
End Sub
 
Private Sub PATextBox71_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(29, 5) = PATextBox71.Text
End Sub
 
Private Sub PATextBox72_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(30, 5) = PATextBox72.Text
End Sub
 
Private Sub PATextBox73_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(31, 5) = PATextBox73.Text
End Sub
 
Private Sub PATextBox74_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(32, 5) = PATextBox74.Text
End Sub
 
Private Sub PATextBox75_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(33, 5) = PATextBox75.Text
End Sub
 
Private Sub PATextBox76_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(34, 5) = PATextBox76.Text
End Sub
 
Private Sub PATextBox77_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(35, 5) = PATextBox77.Text
End Sub
 
Private Sub PATextBox78_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(36, 5) = PATextBox78.Text
End Sub
 
'*****************************
'Colonne CAGES 2 Signification
'*****************************
Private Sub PATextBox79_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(27, 9) = PATextBox79.Text
End Sub
 
Private Sub PATextBox80_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(28, 9) = PATextBox80.Text
End Sub
 
Private Sub PATextBox81_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(29, 9) = PATextBox81.Text
End Sub
 
Private Sub PATextBox82_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(30, 9) = PATextBox82.Text
End Sub
 
Private Sub PATextBox83_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(31, 9) = PATextBox83.Text
End Sub
 
Private Sub PATextBox84_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(32, 9) = PATextBox84.Text
End Sub
 
Private Sub PATextBox85_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(33, 9) = PATextBox85.Text
End Sub
 
Private Sub PATextBox86_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(34, 9) = PATextBox86.Text
End Sub
 
Private Sub PATextBox87_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(35, 9) = PATextBox87.Text
End Sub
 
Private Sub PATextBox88_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(36, 9) = PATextBox88.Text
End Sub
 
'**********************
'Colonne CAGES 2 Images
'**********************
Private Sub PATextBox89_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(27, 10) = PATextBox89.Text
End Sub
 
Private Sub PATextBox90_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(28, 10) = PATextBox90.Text
End Sub
 
Private Sub PATextBox91_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(29, 10) = PATextBox91.Text
End Sub
 
Private Sub PATextBox92_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(30, 10) = PATextBox92.Text
End Sub
 
Private Sub PATextBox93_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(31, 10) = PATextBox93.Text
End Sub
 
Private Sub PATextBox94_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(32, 10) = PATextBox94.Text
End Sub
 
Private Sub PATextBox95_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(33, 10) = PATextBox95.Text
End Sub
 
Private Sub PATextBox96_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(34, 10) = PATextBox96.Text
End Sub
 
Private Sub PATextBox97_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(35, 10) = PATextBox97.Text
End Sub
 
Private Sub PATextBox98_Change()
    ThisWorkbook.Sheets("Paramètres " & UserForm1.AcLabel18.Caption).Cells(36, 10) = PATextBox98.Text
End Sub

Invité · 23/12/2016, 15h58

Bonjour,

Code Classe1 :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
Public WithEvents txt As MSForms.TextBoxPrivate
Sub txt_Change()
 Range(txt.Tag) = txt.Text
 
End Sub

Code UserForm :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
Private Sub UserForm_Initialize()Dim cn(4) As New Classe1
Private Sub UserForm_Initialize()
For i = 0 To 3
Me.Controls("TextBox" & (i + 1)).Tag = "[" & ThisWorkbook.Name & "]Paramètres " & Me.AcLabel18.Caption & "!" & ThisWorkbook.Sheets("Paramètres " & Me.AcLabel18.Caption).Cells(4, 2).Offset(i).adresse
Set cn(i).txt = Me.Controls("TextBox" & (i + 1))
Next
End Sub
End Sub

**Garlim** · 03/01/2017, 17h11

Hello,

Bonne année 2017.

Merci encore une fois pour ta réponse, je reviens de congé d'hiver, c'est pour ça que je ne réponds que maintenant.

Lorsque je lance la macro après avoir entré le code, il m'écrit :
"Erreur de compilation : Type défini par l'utilisateur non défini" en pointant sur "WithEvents txt As MSForms.TextBoxPrivate"

est-ce qu'il faut activer une bibliothèque particulière pour se servir de ça?

Transformer plusieurs sub en module de classe

Macros et VBA Excel

Vue hybride

Discussions similaires

Partager

Partager