procédure trop grande

luisf · 28 Mai 2008

bonjour
j'ai un petit souci de procédure trop grande pour la raccourcir je voudrais créer une fonction dans lequel il y aurait le code qui suit mais je ne sais pas comment faire

Code:

If Sheets(1).Label9.Caption = "Logement" Then
For i = 1 To 20
    Select Case i
        Case 1
            If ComboBox1.Value = "Contrôle Gros oeuvre" Then
                With Sheets("Contrôle GO")
                    For q = 1 To 4
                        Select Case q
                            Case 1
                                If Sheets(1).ComboBox2.Value = "1" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("golog"))
                                End If
                            Case 2
                                If Sheets(1).ComboBox2.Value = "2" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("golog2"))
                                End If
                            Case 3
                                If Sheets(1).ComboBox2.Value = "3" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("golog3"))
                                End If
                            Case 4
                                If Sheets(1).ComboBox2.Value = "4" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("golog4"))
                                End If
                            End Select
                        Next q
                End With
                TextBox5.Value = Sheets("liste des contrôles").Range("b3").Value
            End If
            
        Case 2
            If ComboBox1.Value = "Contrôle Cloison / Doublage" Then
                With Sheets("Contrôle Cloison Doublage")
                    For q = 1 To 4
                        Select Case q
                            Case 1
                                If Sheets(1).ComboBox2.Value = "1" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("clois1"))
                                End If
                            Case 2
                                If Sheets(1).ComboBox2.Value = "2" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("clois2"))
                                End If
                            Case 3
                                If Sheets(1).ComboBox2.Value = "3" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("clois3"))
                                End If
                            Case 4
                                If Sheets(1).ComboBox2.Value = "4" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("clois4"))
                                End If
                            End Select
                        Next q
                End With
                TextBox5.Value = Sheets("liste des contrôles").Range("b4").Value
            End If
            
        Case 3
            If ComboBox1.Value = "Contrôle Plomberie" Then
                With Sheets("Contrôle Plomberie")
                        For q = 1 To 4
                        Select Case q
                            Case 1
                                If Sheets(1).ComboBox2.Value = "1" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("pl1"))
                                End If
                            Case 2
                                If Sheets(1).ComboBox2.Value = "2" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("pl2"))
                                End If
                            Case 3
                                If Sheets(1).ComboBox2.Value = "3" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("pl3"))
                                End If
                            Case 4
                                If Sheets(1).ComboBox2.Value = "4" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("pl4"))
                                End If
                            End Select
                        Next q
                End With
                TextBox5.Value = Sheets("liste des contrôles").Range("b5").Value
            End If
        
        Case 4
            If ComboBox1.Value = "Contrôle VMC" Then
                With Sheets("Contrôle VMC")
                         For q = 1 To 4
                        Select Case q
                            Case 1
                                If Sheets(1).ComboBox2.Value = "1" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("vmc3"))
                                End If
                            Case 2
                                If Sheets(1).ComboBox2.Value = "2" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("vmc3"))
                                End If
                            Case 3
                                If Sheets(1).ComboBox2.Value = "3" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("vmc3"))
                                End If
                            Case 4
                                If Sheets(1).ComboBox2.Value = "4" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("vmc4"))
                                End If
                            End Select
                        Next q
                End With
                TextBox5.Value = Sheets("liste des contrôles").Range("b6").Value
            End If
        
        Case 5
            If ComboBox1.Value = "Contrôle Menuiserie" Then
               With Sheets("Contrôle menuiserie")
                         For q = 1 To 4
                        Select Case q
                            Case 1
                                If Sheets(1).ComboBox2.Value = "1" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("men1"))
                                End If
                            Case 2
                                If Sheets(1).ComboBox2.Value = "2" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("men2"))
                                End If
                            
                            Case 3
                                If Sheets(1).ComboBox2.Value = "3" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("men"))
                                End If
                                
                            Case 4
                                If Sheets(1).ComboBox2.Value = "4" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("men"))
                                End If
                            
                            End Select
                        Next q
                End With
                TextBox5.Value = Sheets("liste des contrôles").Range("b7").Value
            End If

        Case 6
            If ComboBox1.Value = "Contrôle Carrelage" Then
                With Sheets("Contrôle carrelage")
                        For q = 1 To 4
                        Select Case q
                            Case 1
                                If Sheets(1).ComboBox2.Value = "1" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("car1"))
                                End If
                            Case 2
                                If Sheets(1).ComboBox2.Value = "2" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("car2"))
                                End If
                            Case 3
                                If Sheets(1).ComboBox2.Value = "3" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("car3"))
                                End If
                            Case 4
                                If Sheets(1).ComboBox2.Value = "4" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("car4"))
                                End If
                            End Select
                        Next q
                End With
                TextBox5.Value = Sheets("liste des contrôles").Range("b8").Value
            End If
           
            Case 7
            If ComboBox1.Value = "Contrôle Pose des châssis PVC façade" Then
                With Sheets("Contrôle châssis PVC")
                     For q = 1 To 4
                        Select Case q
                            Case 1
                                If Sheets(1).ComboBox2.Value = "1" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("pvc3"))
                                End If
                            Case 2
                                If Sheets(1).ComboBox2.Value = "2" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("pvc3"))
                                End If
                            Case 3
                                If Sheets(1).ComboBox2.Value = "3" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("pvc3"))
                                End If
                            Case 4
                                If Sheets(1).ComboBox2.Value = "4" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("pvc4"))
                                End If
                            End Select
                        Next q
                End With
                TextBox5.Value = Sheets("liste des contrôles").Range("b9").Value
            End If
            
            Case 8
            If ComboBox1.Value = "Contrôle Electricité" Then
                With Sheets("Contrôle électricité")
                        For q = 1 To 4
                        Select Case q
                            Case 1
                                If Sheets(1).ComboBox2.Value = "1" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("elec2"))
                                End If
                            Case 2
                                If Sheets(1).ComboBox2.Value = "2" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("elec2"))
                                End If
                            Case 3
                                If Sheets(1).ComboBox2.Value = "3" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("elec3"))
                                End If
                            Case 4
                                If Sheets(1).ComboBox2.Value = "4" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("elec"))
                                End If
                            End Select
                        Next q
                End With
                TextBox5.Value = Sheets("liste des contrôles").Range("b10").Value
            End If
            
            Case 9
            If ComboBox1.Value = "Contrôle Peinture" Then
                With Sheets("Contrôle peinture")
                        For q = 1 To 4
                        Select Case q
                            Case 1
                                If Sheets(1).ComboBox2.Value = "1" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("peint2"))
                                End If
                            Case 1
                                If Sheets(1).ComboBox2.Value = "2" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("peint2"))
                                End If
                            Case 2
                                If Sheets(1).ComboBox2.Value = "3" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("peint"))
                                End If
                            Case 3
                                If Sheets(1).ComboBox2.Value = "4" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("peint"))
                                End If
                            End Select
                        Next q
                End With
                TextBox5.Value = Sheets("liste des contrôles").Range("b11").Value
            End If
            
            Case 10
            If ComboBox1.Value = "Contrôle Sol souple" Then
                With Sheets("Contrôle sol souple")
                        Me.ComboBox2.List = Application.Transpose(Feuil13.Range("sols"))
                End With
                TextBox5.Value = Sheets("liste des contrôles").Range("b12").Value
            End If
            
            Case 11
            If ComboBox1.Value = "Contrôle Charpente" Then
                With Sheets("Contrôle charpente")
                        For q = 1 To 4
                        Select Case q
                            Case 1
                                If Sheets(1).ComboBox2.Value = "1" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("char2"))
                                End If
                            Case 2
                                If Sheets(1).ComboBox2.Value = "2" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("char2"))
                                End If
                            Case 3
                                If Sheets(1).ComboBox2.Value = "3" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("char3"))
                                End If
                            Case 4
                                 If Sheets(1).ComboBox2.Value = "4" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("char4"))
                                End If
                            End Select
                        Next q
                End With
                TextBox5.Value = Sheets("liste des contrôles").Range("b13").Value
            End If
            
            Case 12
            If ComboBox1.Value = "Contrôle Couverture" Then
                With Sheets("Contrôle couverture")
                       For q = 1 To 4
                        Select Case q
                            Case 1
                                If Sheets(1).ComboBox2.Value = "1" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("couv2"))
                                End If
                            Case 2
                                If Sheets(1).ComboBox2.Value = "2" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("couv2"))
                                End If
                            Case 2
                                If Sheets(1).ComboBox2.Value = "3" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("couv"))
                                End If
                            Case 3
                                If Sheets(1).ComboBox2.Value = "4" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("couv"))
                                End If
                            End Select
                        Next q
                End With
                TextBox5.Value = Sheets("liste des contrôles").Range("b14").Value
            End If
            
            Case 13
            If ComboBox1.Value = "Contrôle Ravalement" Then
                With Sheets("Contrôle ravalement")
                        For q = 1 To 4
                        Select Case q
                            Case 1
                                If Sheets(1).ComboBox2.Value = "1" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("rav3"))
                                End If
                            Case 2
                                If Sheets(1).ComboBox2.Value = "2" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("rav3"))
                                End If
                            Case 3
                                If Sheets(1).ComboBox2.Value = "3" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("rav3"))
                                End If
                            Case 4
                                If Sheets(1).ComboBox2.Value = "4" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("rav"))
                                End If
                            End Select
                        Next q
                End With
                TextBox5.Value = Sheets("liste des contrôles").Range("b15").Value
            End If
            
            Case 14
            If ComboBox1.Value = "Contrôle Etanchéité" Then
                With Sheets("Contrôle étanchéité")
                        For q = 1 To 4
                        Select Case q
                            Case 1
                                If Sheets(1).ComboBox2.Value = "1" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("eta2"))
                                End If
                            Case 2
                                If Sheets(1).ComboBox2.Value = "2" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("eta2"))
                                End If
                            Case 3
                                If Sheets(1).ComboBox2.Value = "3" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("eta3"))
                                End If
                            Case 4
                                If Sheets(1).ComboBox2.Value = "4" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("eta4"))
                                End If
                            End Select
                        Next q
                End With
                TextBox5.Value = Sheets("liste des contrôles").Range("b16").Value
            End If
            
            Case 15
            If ComboBox1.Value = "Contrôle Serrurerie" Then
                With Sheets("Contrôle serrurerie")
                        For q = 1 To 4
                        Select Case q
                            Case 1
                                If Sheets(1).ComboBox2.Value = "1" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("ser3"))
                                End If
                            Case 2
                                If Sheets(1).ComboBox2.Value = "2" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("ser3"))
                                End If
                            Case 3
                                If Sheets(1).ComboBox2.Value = "3" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("ser3"))
                                End If
                            Case 4
                                If Sheets(1).ComboBox2.Value = "4" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("ser"))
                                End If
                            
                            End Select
                        Next q
                End With
                TextBox5.Value = Sheets("liste des contrôles").Range("b17").Value
            End If
            
            Case 16
            If ComboBox1.Value = "Contrôle Faux plafond" Then
                With Sheets("Contrôle Faux plafond")
                        Me.ComboBox2.List = Application.Transpose(Range("fxplaf"))
                End With
                TextBox5.Value = Sheets("liste des contrôles").Range("b18").Value
            End If
            
            Case 17
            If ComboBox1.Value = "Contrôle Bardage" Then
                With Sheets("Contrôle Bardage")
                        Me.ComboBox2.List = Application.Transpose(Range("bardp"))
                End With
                TextBox5.Value = Sheets("liste des contrôles").Range("b19").Value
            End If
            
            Case 18
            If ComboBox1.Value = "Contrôle Cuvelage" Then
                With Sheets("Contrôle Cuvelage")
                        Me.ComboBox2.List = Application.Transpose(Range("cuvs"))
                End With
                TextBox5.Value = Sheets("liste des contrôles").Range("b20").Value
            End If
            
            Case 19
            If ComboBox1.Value = "Contrôle VRD" Then
                With Sheets("Contrôle VRD")
                        Me.ComboBox2.List = Application.Transpose(Range(""))
                End With
                TextBox5.Value = Sheets("liste des contrôles").Range("b21").Value
            End If
            
            Case 20
            If ComboBox1.Value = "Contrôle Maçonnerie" Then
                With Sheets("Contrôle Maçonnerie")
                        For q = 1 To 4
                        Select Case q
                            Case 1
                                If Sheets(1).ComboBox2.Value = "1" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("macl2"))
                                End If
                            Case 2
                                If Sheets(1).ComboBox2.Value = "2" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("macl2"))
                                End If
                            Case 3
                                If Sheets(1).ComboBox2.Value = "3" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("macl3"))
                                End If
                            Case 4
                                If Sheets(1).ComboBox2.Value = "4" Then
                                    Me.ComboBox2.List = Application.Transpose(Range("macl4"))
                                End If
                            
                            End Select
                        Next q
                End With
                TextBox5.Value = Sheets("liste des contrôles").Range("b22").Value
                End If
    End Select
Next i
End If

merci

Cousinhub · 28 Mai 2008

Re : procédure trop grande

Bonsoir,

un peu indigeste, ton code....

Sans pouvoir tester, tu pourrais essayer comme ceci :

Code:

If Sheets(1).Label9.Caption = "Logement" Then
Select Case ComboBox1.Value
        Case "Contrôle Gros oeuvre"
            Me.ComboBox2.List = Application.Transpose(Range("golog" & Sheets(1).ComboBox2.Value))
            TextBox5.Value = Sheets("liste des contrôles").Range("b3").Value
.......
.........

Compte Supprimé 979 · 28 Mai 2008

Re : procédure trop grande

Salut,

Difficile comme ça d'analyser le code sans fichier, mais bon 🙄

1) A quoi te sert

Code:

For i = 1 To 20
  Select Case i

Pour moi ça n'a aucun intérêt de fair 20 fois la même boucle, sachant que tu fait un test systématiquement sur le ComboBox1

2) tu peux réduire le code dans les 'Case' comme ça

Code:

      If ComboBox1.Value = "Contrôle Gros oeuvre" Then
        With Sheets("Contrôle GO")
          For Q = 1 To 4
            If Q + 1 Then
' Apparemment ICI la 1ère plage de nom n'est pas nommée comme pour les autres
              Me.ComboBox2.List = Application.Transpose(Range("golog"))
            Else
' Sinon il suffit de concaténer le nom de la plage et la valeur de 'Q'
              Me.ComboBox2.List = Application.Transpose(Range("golog" & Q))
            End If
          Next Q
        End With
        TextBox5.Value = Sheets("liste des contrôles").Range("b3").Value
      End If

A+

Edit : Oups, salut à toi BhBh 😀

luisf · 28 Mai 2008

Re : procédure trop grande

oui je me doute que comme sa sans fichier c pas très évident.

je ne comprend pas trop ton code bhbh je ne vois pas comment mettre les differentes listes golog, golog2, golog3, golog4

en ce qui concerne le:

Code:

For i = 1 To 20
  Select Case i

je ne sais pas je pensais qu'il fallait le mettre mais si tu me di que cela n'est pas utile alor sa surcharge mon code pour rien.

je prend vos 2 proposition et je me penche serieusement la dessus demain car la je v devoir y aller.

merci pour vos réponse je vous tiens au courant.

Cousinhub · 28 Mai 2008

Re : procédure trop grande

Re-,
Salut Bruno 🙂

Pour ton nom golog, pense à nommer le premier "golog1", comme tu l'as fait pour tous les autres noms (clois1, pl1....)

Si ton combobox 2 de la feuille qui va bien contient 1, cela devient :

Code:

Range("golog" & Sheets(1).ComboBox2.Value)

donc golog1

luisf · 28 Mai 2008

Re : procédure trop grande

ok j'ai compris ce que tu voulais dire
je vois sa demain
merci

Effectuez une recherche sur Excel Downloads...

procédure trop grande

luisf

XLDnaute Occasionnel

Cousinhub

XLDnaute Barbatruc

Compte Supprimé 979

Guest

luisf

XLDnaute Occasionnel

Cousinhub

XLDnaute Barbatruc

luisf

XLDnaute Occasionnel

Discussions similaires

Nous accordons de l'importance à votre vie privée