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