bonjour
voila le code ci-dessous qui me sert , entre autre, à initialise des combobox.
COmme le nombre de ligne de la feuil2 augmente au fur et à mesure de la saisie, il y at'il un moyen optimiser ce 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 Private Sub CommandButton4_Click() 'statistique Unload UserForm1 Load UserForm1 'charge le usf1 UserForm1.Frame4.Caption = " SELECTIONNER LES CRITERES POUR LE CALCUL DU COUT ET DU NOMBRE D'INTERVENTION" UserForm1.Frame6.Visible = True 'montre la frame2 UserForm1.Frame4.Visible = True 'montre la frame2 UserForm1.CommandButton3.Visible = False 'enlever bt3 UserForm1.ComboBox26.Visible = False UserForm1.ComboBox27.Visible = False UserForm1.ComboBox28.Visible = False UserForm1.ComboBox19.Visible = False UserForm1.ComboBox9.Visible = False UserForm1.ComboBox8.Visible = False UserForm1.ComboBox2.Visible = False UserForm1.ComboBox14.Visible = False UserForm1.ComboBox15.Visible = False UserForm1.ComboBox16.Visible = False UserForm1.ComboBox17.Visible = False UserForm1.TextBox1.Visible = False UserForm1.ComboBox4.Visible = False UserForm1.Label33.Visible = False UserForm1.Label26.Visible = False UserForm1.Label6.Visible = False UserForm1.Label17.Visible = False UserForm1.Label16.Visible = False UserForm1.Label6.Visible = False UserForm1.Label5.Visible = False UserForm1.Label12.Visible = False UserForm1.Label14.Visible = False UserForm1.Label15.Visible = False UserForm1.Label22.Visible = False UserForm1.Label37.Visible = False UserForm1.Label36.Visible = False UserForm1.Label7.Visible = False 'change la couleur des combobox servant à la construction du filtre UserForm1.ComboBox24.BackColor = &H80FF80 UserForm1.ComboBox6.BackColor = &H80FF80 UserForm1.ComboBox5.BackColor = &H80FF80 UserForm1.ComboBox3.BackColor = &H80FF80 UserForm1.ComboBox22.BackColor = &H80FF80 UserForm1.ComboBox23.BackColor = &H80FF80 UserForm1.ComboBox4.BackColor = &H80FF80 'initialisation UserForm1.TextBox1.Value = "" UserForm1.ComboBox4.Value = "" UserForm1.ComboBox19.Value = "" UserForm1.TextBox7.Value = 0 & "" UserForm1.TextBox8.Value = 0 Dim j24 As Integer UserForm1.ComboBox24.AddItem UserForm1.ComboBox6.AddItem UserForm1.ComboBox5.AddItem UserForm1.ComboBox3.AddItem UserForm1.ComboBox23.AddItem UserForm1.ComboBox22.AddItem For j24 = 4 To Sheets("feuil2").Range("b65536").End(xlUp).Row UserForm1.ComboBox24 = Sheets("feuil2").Range("b" & j24) UserForm1.ComboBox6 = Sheets("feuil2").Range("a" & j24) UserForm1.ComboBox5 = Sheets("feuil2").Range("d" & j24) UserForm1.ComboBox3 = Sheets("feuil2").Range("e" & j24) UserForm1.ComboBox23 = Sheets("feuil2").Range("i" & j24) UserForm1.ComboBox22 = Sheets("feuil2").Range("k" & j24) If UserForm1.ComboBox24.ListIndex = -1 Then UserForm1.ComboBox24.AddItem Sheets("feuil2").Range("b" & j24) If UserForm1.ComboBox6.ListIndex = -1 Then UserForm1.ComboBox6.AddItem Sheets("feuil2").Range("a" & j24) If UserForm1.ComboBox5.ListIndex = -1 Then UserForm1.ComboBox5.AddItem Sheets("feuil2").Range("d" & j24) If UserForm1.ComboBox3.ListIndex = -1 Then UserForm1.ComboBox3.AddItem Sheets("feuil2").Range("e" & j24) If UserForm1.ComboBox23.ListIndex = -1 Then UserForm1.ComboBox23.AddItem Sheets("feuil2").Range("i" & j24) If UserForm1.ComboBox22.ListIndex = -1 Then UserForm1.ComboBox22.AddItem Sheets("feuil2").Range("k" & j24) Next j24 UserForm1.ComboBox24.ListIndex = 0 UserForm1.ComboBox6.ListIndex = 0 UserForm1.ComboBox5.ListIndex = 0 UserForm1.ComboBox3.ListIndex = 0 UserForm1.ComboBox23.ListIndex = 0 UserForm1.ComboBox22.ListIndex = 0 UserForm1.filtrestatistiquecout UserForm1.Show 'montre le usf1 End Sub
merci
Partager