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
merci
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