Procedure too large
Bonjour,
Y'a til une idee pour eviter cet erreur "Procedure too large"
j'ai cree des drop down lists sur 10 cellules qui contiennent des codes et une fois on clique sur n'importe quel code de cette liste , on aura sa défintion dans la cellule juste en haut , ça fonctionne très bien pour les 10 premiéres cellules
mais je dois refaire la même chose pour d'autres cellules notons que c'est les mêmes codes et les mêmes définitions.malheureusement j'ai eu cet erreur qui me permettera pas de continuer
voici une petite aperçu de mon code , je voulais savoir si y'a til une maniére pour éviter quelques lignes de code
Merci
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "X4" Then
Select Case Target
Case "Educ": range1 = "B7"
Case "EX1": range1 = "B25"
Case "EX2": range1 = "B26"
Case "EX3": range1 = "B27"
Case "EX4": range1 = "B28"
Case "EX5": range1 = "B29"
Case "EX6": range1 = "B30"
Case "EX7": range1 = "B31"
Case "EX8": range1 = "B32"
Case "EX9": range1 = "B33"
Case "EX10": range1 = "B34"
Case "A1": range1 = "B71"
Case "A2": range1 = "B72"
Case "PS1": range1 = "B73"
Case "PS2": range1 = "B74"
Case "AED1": range1 = "B45"
Case "AED2": range1 = "B46"
End Select
End If
If range1 <> "" Then
Sheets("SBR").Range("X3") = _
Sheets("Process Info").Range(range1).Value
End If
If Target.Address(0, 0) = "Y4" Then
Select Case Target
Case "Educ": range1a = "B7"
Case "EX1": range1a = "B25"
Case "EX2": range1a = "B26"
Case "EX3": range1a = "B27"
Case "EX4": range1a = "B28"
Case "EX5": range1a = "B29"
Case "EX6": range1a = "B30"
Case "EX7": range1a = "B31"
Case "EX8": range1a = "B32"
Case "EX9": range1a = "B33"
Case "EX10": range1a = "B34"
Case "A1": range1a = "B71"
Case "A2": range1a = "B72"
Case "PS1": range1a = "B73"
Case "PS2": range1a = "B74"
Case "AED1": range1a = "B45"
Case "AED2": range1a = "B46"
End Select
End If
If range1a <> "" Then
Sheets("SBR").Range("Y3") = _
Sheets("Process Info").Range(range1a).Value
End If
If Target.Address(0, 0) = "Z4" Then
Select Case Target
Case "Educ": range1b = "B7"
Case "EX1": range1b = "B25"
Case "EX2": range1b = "B26"
Case "EX3": range1b = "B27"
Case "EX4": range1b = "B28"
Case "EX5": range1b = "B29"
Case "EX6": range1b = "B30"
Case "EX7": range1b = "B31"
Case "EX8": range1b = "B32"
Case "EX9": range1b = "B33"
Case "EX10": range1b = "B34"
Case "A1": range1b = "B71"
Case "A2": range1b = "B72"
Case "PS1": range1b = "B73"
Case "PS2": range1b = "B74"
Case "AED1": range1b = "B45"
Case "AED2": range1b = "B46"
End Select
End If
If range1b <> "" Then
Sheets("SBR").Range("Z3") = _
Sheets("Process Info").Range(range1b).Value
End If
If Target.Address(0, 0) = "AA4" Then
Select Case Target
Case "Educ": range1c = "B7"
Case "EX1": range1c = "B25"
Case "EX2": range1c = "B26"
Case "EX3": range1c = "B27"
Case "EX4": range1c = "B28"
Case "EX5": range1c = "B29"
Case "EX6": range1c = "B30"
Case "EX7": range1c = "B31"
Case "EX8": range1c = "B32"
Case "EX9": range1c = "B33"
Case "EX10": range1c = "B34"
Case "A1": range1c = "B71"
Case "A2": range1c = "B72"
Case "PS1": range1c = "B73"
Case "PS2": range1c = "B74"
Case "AED1": range1c = "B45"
Case "AED2": range1c = "B46"
End Select
End If
If range1c <> "" Then
Sheets("SBR").Range("AA3") = _
Sheets("Process Info").Range(range1c).Value
End If
If Target.Address(0, 0) = "AB4" Then
Select Case Target
Case "Educ": range1x = "B7"
Case "EX1": range1x = "B25"
Case "EX2": range1x = "B26"
Case "EX3": range1x = "B27"
Case "EX4": range1x = "B28"
Case "EX5": range1x = "B29"
Case "EX6": range1x = "B30"
Case "EX7": range1x = "B31"
Case "EX8": range1x = "B32"
Case "EX9": range1x = "B33"
Case "EX10": range1x = "B34"
Case "A1": range1x = "B71"
Case "A2": range1x = "B72"
Case "PS1": range1x = "B73"
Case "PS2": range1x = "B74"
Case "AED1": range1x = "B45"
Case "AED2": range1x = "B46"
End Select
End If
If range1x <> "" Then
Sheets("SBR").Range("AB3") = _
Sheets("Process Info").Range(range1x).Value
End If
If Target.Address(0, 0) = "AB4" Then
Select Case Target
Case "Educ": range1z = "B7"
Case "EX1": range1z = "B25"
Case "EX2": range1z = "B26"
Case "EX3": range1z = "B27"
Case "EX4": range1z = "B28"
Case "EX5": range1z = "B29"
Case "EX6": range1z = "B30"
Case "EX7": range1z = "B31"
Case "EX8": range1z = "B32"
Case "EX9": range1z = "B33"
Case "EX10": range1z = "B34"
Case "A1": range1z = "B71"
Case "A2": range1z = "B72"
Case "PS1": range1z = "B73"
Case "PS2": range1z = "B74"
Case "AED1": range1z = "B45"
Case "AED2": range1z = "B46"
End Select
End If
If range1z <> "" Then
Sheets("SBR").Range("AB3") = _
Sheets("Process Info").Range(range1z).Value
End If
If Target.Address(0, 0) = "AC4" Then
Select Case Target
Case "Educ": range1s = "B7"
Case "EX1": range1s = "B25"
Case "EX2": range1s = "B26"
Case "EX3": range1s = "B27"
Case "EX4": range1s = "B28"
Case "EX5": range1s = "B29"
Case "EX6": range1s = "B30"
Case "EX7": range1s = "B31"
Case "EX8": range1s = "B32"
Case "EX9": range1s = "B33"
Case "EX10": range1s = "B34"
Case "A1": range1s = "B71"
Case "A2": range1s = "B72"
Case "PS1": range1s = "B73"
Case "PS2": range1s = "B74"
Case "AED1": range1s = "B45"
Case "AED2": range1s = "B46"
End Select
End If
If Target.Address(0, 0) = "BB4" Then
Select Case Target
Case "K1": sbr3 = "B49"
Case "K2": sbr3 = "B50"
Case "K3": sbr3 = "B51"
Case "K4": sbr3 = "B52"
Case "K5": sbr3 = "B53"
Case "K6": sbr3 = "B54"
Case "K7": sbr3 = "B55"
Case "K8": sbr3 = "B56"
Case "K9": sbr3 = "B57"
Case "K10": sbr3 = "B58"
Case "K11": sbr3 = "B59"
Case "K12": sbr3 = "B60"
Case "K13": sbr3 = "B61"
Case "K14": sbr3 = "B62"
Case "K15": sbr3 = "B63"
Case "K16": sbr3 = "B64"
Case "K17": sbr3 = "B65"
Case "K18": sbr3 = "B66"
Case "K19": sbr3 = "B67"
Case "K20": sbr3 = "B68"
Case "A1": sbr3 = "B71"
Case "A2": sbr3 = "B72"
Case "A3": sbr3 = "B73"
Case "A4": sbr3 = "B74"
Case "A5": sbr3 = "B75"
Case "A6": sbr3 = "B76"
Case "A7": sbr3 = "B77"
Case "A8": sbr3 = "B78"
Case "A9": sbr3 = "B79"
Case "A10": sbr3 = "B80"
Case "PS1": sbr3 = "B83"
Case "PS2": sbr3 = "B84"
Case "PS3": sbr3 = "B85"
Case "PS4": sbr3 = "B86"
Case "PS5": sbr3 = "B87"
Case "PS6": sbr3 = "B88"
Case "PS7": sbr3 = "B89"
Case "PS8": sbr3 = "B90"
Case "PS9": sbr3 = "B91"
Case "PS10": sbr3 = "B92"
Case "Cmp1": sbr3 = "B95"
Case "Cmp2": sbr3 = "B96"
Case "Cmp3": sbr3 = "B97"
Case "Cmp4": sbr3 = "B98"
Case "Cmp5": sbr3 = "B99"
Case "Cmp6": sbr3 = "B100"
Case "Cmp7": sbr3 = "B101"
Case "Cmp8": sbr3 = "B102"
Case "Cmp9": sbr3 = "B103"
Case "Cmp10": sbr3 = "B104"
Case "Cmp11": sbr3 = "B107"
Case "Cmp12": sbr3 = "B108"
Case "Cmp13": sbr3 = "B109"
Case "Cmp14": sbr3 = "B110"
Case "Cmp15": sbr3 = "B111"
End Select
End If
If sbr3 <> "" Then
Sheets("SBR").Range("BA3") = _
Sheets("Process Info").Range(sbr3).Value
End If
If Target.Address(0, 0) = "BD4" Then
Select Case Target
Case "K1": sbr4 = "B49"
Case "K2": sbr4 = "B50"
Case "K3": sbr4 = "B51"
Case "K4": sbr4 = "B52"
Case "K5": sbr4 = "B53"
Case "K6": sbr4 = "B54"
Case "K7": sbr4 = "B55"
Case "K8": sbr4 = "B56"
Case "K9": sbr4 = "B57"
Case "K10": sbr4 = "B58"
Case "K11": sbr4 = "B59"
Case "K12": sbr4 = "B60"
Case "K13": sbr4 = "B61"
Case "K14": sbr4 = "B62"
Case "K15": sbr4 = "B63"
Case "K16": sbr4 = "B64"
Case "K17": sbr4 = "B65"
Case "K18": sbr4 = "B66"
Case "K19": sbr4 = "B67"
Case "K20": sbr4 = "B68"
Case "A1": sbr4 = "B71"
Case "A2": sbr4 = "B72"
Case "A3": sbr4 = "B73"
Case "A4": sbr4 = "B74"
Case "A5": sbr4 = "B75"
Case "A6": sbr4 = "B76"
Case "A7": sbr4 = "B77"
Case "A8": sbr4 = "B78"
Case "A9": sbr4 = "B79"
Case "A10": sbr4 = "B80"
Case "PS1": sbr4 = "B83"
Case "PS2": sbr4 = "B84"
Case "PS3": sbr4 = "B85"
Case "PS4": sbr4 = "B86"
Case "PS5": sbr4 = "B87"
Case "PS6": sbr4 = "B88"
Case "PS7": sbr4 = "B89"
Case "PS8": sbr4 = "B90"
Case "PS9": sbr4 = "B91"
Case "PS10": sbr4 = "B92"
Case "Cmp1": sbr4 = "B95"
Case "Cmp2": sbr4 = "B96"
Case "Cmp3": sbr4 = "B97"
Case "Cmp4": sbr4 = "B98"
Case "Cmp5": sbr4 = "B99"
Case "Cmp6": sbr4 = "B100"
Case "Cmp7": sbr4 = "B101"
Case "Cmp8": sbr4 = "B102"
Case "Cmp9": sbr4 = "B103"
Case "Cmp10": sbr4 = "B104"
Case "Cmp11": sbr4 = "B107"
Case "Cmp12": sbr4 = "B108"
Case "Cmp13": sbr4 = "B109"
Case "Cmp14": sbr4 = "B110"
Case "Cmp15": sbr4 = "B111"
End Select
End If
If sbr4 <> "" Then
Sheets("SBR").Range("BC3") = _
Sheets("Process Info").Range(sbr4).Value
End If
If Target.Address(0, 0) = "BF4" Then
Select Case Target
Case "K1": sbr2 = "B49"
Case "K2": sbR5 = "B50"
Case "K3": sbR5 = "B51"
Case "K4": sbR5 = "B52"
Case "K5": sbR5 = "B53"
Case "K6": sbR5 = "B54"
Case "K7": sbR5 = "B55"
Case "K8": sbR5 = "B56"
Case "K9": sbR5 = "B57"
Case "K10": sbR5 = "B58"
Case "K11": sbR5 = "B59"
Case "K12": sbR5 = "B60"
Case "K13": sbR5 = "B61"
Case "K14": sbR5 = "B62"
Case "K15": sbR5 = "B63"
Case "K16": sbR5 = "B64"
Case "K17": sbR5 = "B65"
Case "K18": sbR5 = "B66"
Case "K19": sbR5 = "B67"
Case "K20": sbR5 = "B68"
Case "A1": sbR5 = "B71"
Case "A2": sbR5 = "B72"
Case "A3": sbR5 = "B73"
Case "A4": sbR5 = "B74"
Case "A5": sbR5 = "B75"
Case "A6": sbR5 = "B76"
Case "A7": sbR5 = "B77"
Case "A8": sbR5 = "B78"
Case "A9": sbR5 = "B79"
Case "A10": sbR5 = "B80"
Case "PS1": sbR5 = "B83"
Case "PS2": sbR5 = "B84"
Case "PS3": sbR5 = "B85"
Case "PS4": sbR5 = "B86"
Case "PS5": sbR5 = "B87"
Case "PS6": sbR5 = "B88"
Case "PS7": sbR5 = "B89"
Case "PS8": sbR5 = "B90"
Case "PS9": sbR5 = "B91"
Case "PS10": sbR5 = "B92"
Case "Cmp1": sbR5 = "B95"
Case "Cmp2": sbR5 = "B96"
Case "Cmp3": sbR5 = "B97"
Case "Cmp4": sbR5 = "B98"
Case "Cmp5": sbR5 = "B99"
Case "Cmp6": sbR5 = "B100"
Case "Cmp7": sbR5 = "B101"
Case "Cmp8": sbR5 = "B102"
Case "Cmp9": sbR5 = "B103"
Case "Cmp10": sbR5 = "B104"
Case "Cmp11": sbR5 = "B107"
Case "Cmp12": sbR5 = "B108"
Case "Cmp13": sbR5 = "B109"
Case "Cmp14": sbR5 = "B110"
Case "Cmp15": sbR5 = "B111"
End Select
End If
If sbR5 <> "" Then
Sheets("SBR").Range("BE3") = _
Sheets("Process Info").Range(sbR5).Value
End If
If Target.Address(0, 0) = "BH4" Then
Select Case Target
Case "K1": sbr6 = "B49"
Case "K2": sbr6 = "B50"
Case "K3": sbr6 = "B51"
Case "K4": sbr6 = "B52"
Case "K5": sbr6 = "B53"
Case "K6": sbr6 = "B54"
Case "K7": sbr6 = "B55"
Case "K8": sbr6 = "B56"
Case "K9": sbr6 = "B57"
Case "K10": sbr6 = "B58"
Case "K11": sbr6 = "B59"
Case "K12": sbr6 = "B60"
Case "K13": sbr6 = "B61"
Case "K14": sbr6 = "B62"
Case "K15": sbr6 = "B63"
Case "K16": sbr6 = "B64"
Case "K17": sbr6 = "B65"
Case "K18": sbr6 = "B66"
Case "K19": sbr6 = "B67"
Case "K20": sbr6 = "B68"
Case "A1": sbr6 = "B71"
Case "A2": sbr6 = "B72"
Case "A3": sbr6 = "B73"
Case "A4": sbr6 = "B74"
Case "A5": sbr6 = "B75"
Case "A6": sbr6 = "B76"
Case "A7": sbr6 = "B77"
Case "A8": sbr6 = "B78"
Case "A9": sbr6 = "B79"
Case "A10": sbr6 = "B80"
Case "PS1": sbr6 = "B83"
Case "PS2": sbr6 = "B84"
Case "PS3": sbr6 = "B85"
Case "PS4": sbr6 = "B86"
Case "PS5": sbr6 = "B87"
Case "PS6": sbr6 = "B88"
Case "PS7": sbr6 = "B89"
Case "PS8": sbr6 = "B90"
Case "PS9": sbr6 = "B91"
Case "PS10": sbr6 = "B92"
Case "Cmp1": sbr6 = "B95"
Case "Cmp2": sbr6 = "B96"
Case "Cmp3": sbr6 = "B97"
Case "Cmp4": sbr6 = "B98"
Case "Cmp5": sbr6 = "B99"
Case "Cmp6": sbr6 = "B100"
Case "Cmp7": sbr6 = "B101"
Case "Cmp8": sbr6 = "B102"
Case "Cmp9": sbr6 = "B103"
Case "Cmp10": sbr6 = "B104"
Case "Cmp11": sbr6 = "B107"
Case "Cmp12": sbr6 = "B108"
Case "Cmp13": sbr6 = "B109"
Case "Cmp14": sbr6 = "B110"
Case "Cmp15": sbr6 = "B111"
End Select
End If
Bonjour,
Ouf !
Cordialement.
Je t'ai pas obligé de me répondre.
Et alors !
De toute façon ce n'était ce n'était juste qu'un constat ! L'aspect positif du problème est que cela devrait inciter à programmer de façon un peu plus modulaire d'une part et à rechercher des algorithmes propres à réduire sensiblement le volume de code pour un même résultat. A progresser donc !
Je ne donne certes pas de solution tout faite mais l'orientation est bien la bonne.
Cordialement.