optimisation du code pour des combobox

**oscar.cesar** · 06/03/2008, 20h24

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

**Singular** · 06/03/2008, 21h15

Oublies ta boucle avec j24...

Code :

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

1
2
3
4
5
6
7
8
9
    Dim lDerniereLigne As Long
 
    With Sheets("feuil2")
        lDerniereLigne = .Range("b65536").End(xlUp).Row
        UserForm1.ComboBox24.List = .Range("b4:b" & lDerniereLigne).Value
        UserForm1.ComboBox6.List = .Range("a4:a" & lDerniereLigne).Value
        UserForm1.ComboBox5.List = .Range("d4:d" & lDerniereLigne).Value
        ...
    End With

**oscar.cesar** · 07/03/2008, 07h18

bonjour
merci de ta suggestion mais il me reste les doublons à regler .

il faut savoir que je cherche à optimise cette procedure car j'ai 7 combobox que j'ai remplir .en eliminant les doublons.

Du fait que le nombre de ligne de ma feuille2 augmente au fur et à mesure, lors de l'appel des procedures concernées, le temps d'attente et de plus en plus long!!!!!!!

Code :

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

1
2
3
4
5
6
7
8
9
 
' le combobx19  hors contrat en supprimant les doublons eventuel
UserForm1.ComboBox19.AddItem
Dim j19 As Integer
For j19 = 4 To Sheets("feuil2").Range("j65536").End(xlUp).Row
UserForm1.ComboBox19 = Sheets("feuil2").Range("j" & j19)
If UserForm1.ComboBox19.ListIndex = -1 Then UserForm1.ComboBox19.AddItem Sheets("feuil2").Range("j" & j19)
Next j19
UserForm1.ComboBox19.ListIndex = 0

**fring** · 07/03/2008, 09h35

Bonjour,

Si je comprend bien le code, la boucle prend une à une les lignes de la colonne j et les insère dans le Combo.

Pourquoi ne pas utiliser la propriété RowSource ?

Code :

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

1
2
3
4
With Sheets("feuil2")
x = .Cells(Rows.Count, 10).End(xlUp).Row
UserForm1.ComboBox19.RowSource = .Range("J4:J" & x)
End With

Et ainsi de suite pour les autres Combo, à moins que j'ai loupé un élément.

**tomy7** · 07/03/2008, 09h48

pour tes doublons: tu as des exemples comme celui-ci

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
Voici un exemple où 3 combobox sont remplies
en fonction des données en colonne A, B, C.
 
Pour en remplir d'autres, tu invoques RempliComboUnik avec les deux bons
paramètres: la plage source et le combo de destination.
 
 
Sub EssaiComboFillUniq()
RempliComboUnik Sheets("Feuil1").Range("A1:A" & _
Sheets("Feuil1").Range("A65536").End(xlUp).Row), _
Worksheets("BD").ComboBox1
RempliComboUnik Sheets("Feuil1").Range("B1:B" & _
Sheets("Feuil1").Range("B65536").End(xlUp).Row), _
Worksheets("BD").ComboBox2
RempliComboUnik Sheets("Feuil1").Range("C1:C" & _
Sheets("Feuil1").Range("C65536").End(xlUp).Row), _
Worksheets("BD").ComboBox3
End Sub
 
Sub RempliComboUnik(Plage As Range, QuelCombo As MSForms.ComboBox)
 
Dim C As Range
Dim Tbl As New Collection
Dim i As Integer
 
On Error Resume Next
For Each C In Plage
If Not IsError(C) Then
If C<>"" Then Tbl.Add C.Value, CStr(C.Value)
End If
Next C
On Error GoTo 0
 
With QuelCombo
.Clear
For i = 1 To Tbl.Count
.AddItem Tbl(i)
Next i
.ListIndex = 0
End With
 
Set Tbl = Nothing
 
End Sub

**oscar.cesar** · 07/03/2008, 09h52

bonjour
Fring
au depart , cela etait interessant mais il faut que je elimine les doublons.

je regarde vos codes

**ouskel'n'or** · 07/03/2008, 10h17

Pour dire quelque chose... qui raccourcit le code... mais n'optimise rien...

Code :

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

1
2
3
4
5
6
7
8
9
10
Dim combos, labels
    combos = Array("26", "27", "28", "19", "9", "8", "2", "14", "15", "16", "17", "4")
    For i = 0 To UBound(combos) - 1
        Me.Controls("ComboBox" & combos(i)).Visible = False
    Next
    Me.TextBox1.Visible = False
    labels = Array("33", "26", "6", "17", "16", "6", "5", "12", "14", "15", "22", "37", "36", "7")
    For i = 0 To UBound(labels) - 1
        Me.Controls("Label" & labels(i)).Visible = False
    Next

**oscar.cesar** · 07/03/2008, 18h37

bonjour
voila je vins de faire l'essai avec 500 lignes
les remplissages de mes 7 combobox prend plus de 45s.
trop long en sachant que le nb de ligne devrait avoisine les 5000 à la fin 2008.

il faut que je trouve autre chose ?????

Si quelqu'un à des idees??????

Je" m'oriente , peut etre

creer a bounton dans mon menu qui lancerai la recherche dans les diverses colonnes des valeurs sans doublons et les recopierait dans une feuil3 une colonne par type de donnnée(ici 7 )
puis je reaffecte la propriete rowsource de chaque combobox à chaque colonne !!!!

bon chais pas si c'est plus top ?

**fring** · 07/03/2008, 18h58

A tout hazard, réessaye ton code en y insérant les lignes suivantes en début et fin de code

Code :

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

1
2
3
4
5
6
7
8
9
Sub tamacro()
Application.ScreenUpdating = False 
Application.Calculation = xlCalculationManual 
'***********************
' ton code
'***********************
Application.Calculation = xlCalculationAutomatic 
Application.ScreenUpdating = true
End Sub

**oscar.cesar** · 07/03/2008, 23h06

bonsoir
apres discussion avec les futurs utilisateurs , l'idee ci-dessus n'ext retenue.
je cherche donc à remplir sans doublon mes 7 combobox le plus rapidement possible ?

**oscar.cesar** · 07/03/2008, 23h38

bon apres avoir examiner mon code la lenteur viendrai pas du remplissage des combobox mais de l'evenement de ces dernieres.

voila le code de remplissage des combobox .
A ce moment je me trouve dans USF1 et les combobox sont dans le USF1

bon le code est peut etre pas top mais bon

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
 
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.Frame1.Visible = True 'rend visible la frame1
UserForm1.Frame1.Enabled = False 'verouille le frame1
 
 
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
 
 
 
 
' le combobx22  entreprise en supprimant les doublons eventuel
' le combobx24type installation en supprimant les doublons eventuel
' le combobx6 N° installation en supprimant les doublons eventuel
' le combobx3  etablissement en supprimant les doublons eventuel
' le combobx23  n°lot en supprimant les doublons eventuel
' le combobx5  site geographique en supprimant les doublons eventuel
   Dim j24 As Integer, derniereligne As Integer
             UserForm1.ComboBox24.AddItem
             UserForm1.ComboBox6.AddItem
             UserForm1.ComboBox5.AddItem
             UserForm1.ComboBox3.AddItem
             UserForm1.ComboBox23.AddItem
             UserForm1.ComboBox22.AddItem
    derniereligne = Sheets("feuil2").Range("m4").End(xlDown).Row 'derniere ligne occupee dans colonne A
       'For j24 = 4 To Sheets("feuil2").Range("b65536").End(xlUp).Row
          For j24 = 4 To derniereligne
 
           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 'demarre de la liste deroulante du combobox en 1 er positon
            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

mais la ou la lenteur semble etre , c'est
dans le USF1 pour chaque combobox (il y en a 6)

j'ai leur reaction sur l'evenement "change"

Code :

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

1
2
3
4
 
Private Sub ComboBox22_Change()
filtrestatistiquecout
End Sub

idem pour les 6 autres.

il me faut donc

1 ) remplir les cbx ca via le code ci-dessus ca doit etre cool
2) par contre faire reagir les cbx sur l'evenement change seulement APRES avoir les avoir rempli via 1)

je sais pas faire

**ucfoutu** · 08/03/2008, 07h55

Bonjour,

- pas très adroit, en effet, d'avoir joué là avec l'évènement Change !

- mais tu peux continuer avec un tel mécanisme à condtion :

1) de déclarer une varuable booléenne globale toto dans la partie générale et
lui donner la valeur True au départ (à l'initialize de ton userform)
2) en tout début du code de ton commandbutton :
- donner la valeur False à toto
3) en fin du code de ton CommandButton
- donner la valeur True à toto
4) dans ton évènement change, subordonner l'exécution à la valeur de toto, ainsi :

Code :

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

1
2
3
4
5
Private Sub ComboBox22_Change()
  if toto then
    filtrestatistiquecout
  end if
End Sub

ou encore (plus adroitement) écrire en toute première ligne de la procédure filtrestatistiquecout :

Code :

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

If not toto then exit sub

s'il s'agit d'une simple procédure
ou

Code :

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

If not toto then exit function

s'il s'agit d'une fonction

**oscar.cesar** · 08/03/2008, 13h30

bonjour
merci ucfoutu

j'ai un peu galerai car j'avais un

Code :

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

1
2
 
UserForm1.Show au milieu

mais ca marche

Si j'ai bien compris c'est au chargement

Code :

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

1
2
 
load userform

du USF que les l'initialisation se fait !

optimisation du code pour des combobox

Macros et VBA Excel

Discussions similaires

Partager

Partager