Hi There ,
I would like to show the definition of each definition placed in a combo box , I use more over 80 drop down list
here is my code ; it was working good but when i add some new definition i got this error (Procedure too large )
I would like to show the definition of each definition placed in a combo box , I use more over 80 drop down list
here is my code ; it was working good but when i add some new definition i got this error (Procedure too large )
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address(0, 0) = "AA4" Then
Select Case Target
Case "EDU1": range1 = "B19"
Case "EDU2": range1 = "B20"
Case "EDU3": range1 = "B21"
Case "EDU4": range1 = "B22"
Case "EXP1": range1 = "B25"
Case "EXP2": range1 = "B26"
Case "EXP3": range1 = "B27"
Case "EXP4": range1 = "B28"
Case "EXP5": range1 = "B29"
Case "EXP6": range1 = "B30"
Case "EXP7": range1 = "B31"
Case "EXP8": range1 = "B32"
Case "EXP9": range1 = "B33"
Case "EXP10": range1 = "B34"
Case "EXP11": range1 = "B35"
Case "EXP12": range1 = "B36"
Case "EXP13": range1 = "B37"
Case "EXP14": range1 = "B38"
Case "EXP15": range1 = "B39"
Case "EXP16": range1 = "B40"
Case "EXP17": range1 = "B41"
Case "EXP18": range1 = "B42"
Case "EXP19": range1 = "B43"
Case "EXP20": range1 = "B44"
Case "A1": range1 = "B71"
Case "A2": range1 = "B72"
Case "PS1": range1 = "B83"
Case "Comp1": range1 = "B95"
Case "Comp2": range1 = "B96"
Case "AEDU1": range1 = "B116"
Case "AEDU2": range1 = "B117"
Case "AEXP1": range1 = "B121"
Case "AEXP2": range1 = "B122"
Case "AEXP3": range1 = "B123"
End Select
End If
If range1 <> "" Then
Sheets("SBR").Range("AA3") = _
Sheets("Process Info").Range(range1).Value
End If
If Target.Address(0, 0) = "AB4" Then
Select Case Target
Case "EDU1": rangeX = "B19"
Case "EDU2": rangeX = "B20"
Case "EDU3": rangeX = "B21"
Case "EDU4": rangeX = "B22"
Case "EXP1": rangeX = "B25"
Case "EXP2": rangeX = "B26"
Case "EXP3": rangeX = "B27"
Case "EXP4": rangeX = "B28"
Case "EXP5": rangeX = "B29"
Case "EXP6": rangeX = "B30"
Case "EXP7": rangeX = "B31"
Case "EXP8": rangeX = "B32"
Case "EXP9": rangeX = "B33"
Case "EXP10": rangeX = "B34"
Case "EXP11": rangeX = "B35"
Case "EXP12": rangeX = "B36"
Case "EXP13": rangeX = "B37"
Case "EXP14": rangeX = "B38"
Case "EXP15": rangeX = "B39"
Case "EXP16": rangeX = "B40"
Case "EXP17": rangeX = "B41"
Case "EXP18": rangeX = "B42"
Case "EXP19": rangeX = "B43"
Case "EXP20": rangeX = "B44"
Case "A1": rangeX = "B71"
Case "A2": rangeX = "B72"
Case "PS1": rangeX = "B83"
Case "Comp1": rangeX = "B95"
Case "Comp2": rangeX = "B96"
Case "AEDU1": rangeX = "B116"
Case "AEDU2": rangeX = "B117"
Case "AEXP1": rangeX = "B121"
Case "AEXP2": rangeX = "B122"
Case "AEXP3": rangeX = "B123"
End Select
End If
If rangeX <> "" Then
Sheets("SBR").Range("AB3") = _
Sheets("Process Info").Range(rangeX).Value
End If
If Target.Address(0, 0) = "AC4" Then
Select Case Target
Case "EDU1": range1a = "B19"
Case "EDU2": range1a = "B20"
Case "EDU3": range1a = "B21"
Case "EDU4": range1a = "B22"
Case "EXP1": range1a = "B25"
Case "EXP2": range1a = "B26"
Case "EXP3": range1a = "B27"
Case "EXP4": range1a = "B28"
Case "EXP5": range1a = "B29"
Case "EXP6": range1a = "B30"
Case "EXP7": range1a = "B31"
Case "EXP8": range1a = "B32"
Case "EXP9": range1a = "B33"
Case "EXP10": range1a = "B34"
Case "EXP11": range1a = "B35"
Case "EXP12": range1a = "B36"
Case "EXP13": range1a = "B37"
Case "EXP14": range1a = "B38"
Case "EXP15": range1a = "B39"
Case "EXP16": range1a = "B40"
Case "EXP17": range1a = "B41"
Case "EXP18": range1a = "B42"
Case "EXP19": range1a = "B43"
Case "EXP20": range1a = "B44"
Case "A1": range1a = "B71"
Case "A2": range1a = "B72"
Case "PS1": range1a = "B83"
Case "Comp1": range1a = "B95"
Case "Comp2": range1a = "B96"
Case "AEDU1": range1a = "B116"
Case "AEDU2": range1a = "B117"
Case "AEXP1": range1a = "B121"
Case "AEXP2": range1a = "B122"
Case "AEXP3": range1a = "B123"
End Select
End If
If range1a <> "" Then
Sheets("SBR").Range("AC3") = _
Sheets("Process Info").Range(range1a).Value
End If
If Target.Address(0, 0) = "Z4" Then
Select Case Target
Case "EDU1": range1a = "B19"
Case "EDU2": range1b = "B20"
Case "EDU3": range1b = "B21"
Case "EDU4": range1b = "B22"
Case "EXP1": range1b = "B25"
Case "EXP2": range1b = "B26"
Case "EXP3": range1b = "B27"
Case "EXP4": range1b = "B28"
Case "EXP5": range1b = "B29"
Case "EXP6": range1b = "B30"
Case "EXP7": range1b = "B31"
Case "EXP8": range1b = "B32"
Case "EXP9": range1b = "B33"
Case "EXP10": range1b = "B34"
Case "EXP11": range1b = "B35"
Case "EXP12": range1b = "B36"
Case "EXP13": range1b = "B37"
Case "EXP14": range1b = "B38"
Case "EXP15": range1b = "B39"
Case "EXP16": range1b = "B40"
Case "EXP17": range1b = "B41"
Case "EXP18": range1b = "B42"
Case "EXP19": range1b = "B43"
Case "EXP20": range1b = "B44"
Case "A1": range1b = "B71"
Case "A2": range1b = "B72"
Case "PS1": range1b = "B83"
Case "Comp1": range1b = "B95"
Case "Comp2": range1b = "B96"
Case "AEDU1": range1b = "B116"
Case "AEDU2": range1b = "B117"
Case "AEXP1": range1b = "B121"
Case "AEXP2": range1b = "B122"
Case "AEXP3": range1b = "B123"
End Select
End If
If range1b <> "" Then
Sheets("SBR").Range("Z3") = _
Sheets("Process Info").Range(range1b).Value
End If
If Target.Address(0, 0) = "AD4" Then
Select Case Target
Case "EDU1": range1c = "B19"
Case "EDU2": range1c = "B20"
Case "EDU3": range1c = "B21"
Case "EDU4": range1c = "B22"
Case "EXP1": range1c = "B25"
Case "EXP2": range1c = "B26"
Case "EXP3": range1c = "B27"
Case "EXP4": range1c = "B28"
Case "EXP5": range1c = "B29"
Case "EXP6": range1c = "B30"
Case "EXP7": range1c = "B31"
Case "EXP8": range1c = "B32"
Case "EXP9": range1c = "B33"
Case "EXP10": range1c = "B34"
Case "EXP11": range1c = "B35"
Case "EXP12": range1c = "B36"
Case "EXP13": range1c = "B37"
Case "EXP14": range1c = "B38"
Case "EXP15": range1c = "B39"
Case "EXP16": range1c = "B40"
Case "EXP17": range1c = "B41"
Case "EXP18": range1c = "B42"
Case "EXP19": range1c = "B43"
Case "EXP20": range1c = "B44"
Case "A1": range1c = "B71"
Case "A2": range1c = "B72"
Case "PS1": range1c = "B83"
Case "Comp1": range1c = "B95"
Case "Comp2": range1c = "B96"
Case "AEDU1": range1c = "B116"
Case "AEDU2": range1c = "B117"
Case "AEXP1": range1c = "B121"
Case "AEXP2": range1c = "B122"
Case "AEXP3": range1c = "B123"
End Select
End If
If range1c <> "" Then
Sheets("SBR").Range("AD3") = _
Sheets("Process Info").Range(range1c).Value
End If
If Target.Address(0, 0) = "AE4" Then
Select Case Target
Case "EDU1": range1x = "B19"
Case "EDU2": range1x = "B20"
Case "EDU3": range1x = "B21"
Case "EDU4": range1x = "B22"
Case "EXP1": range1x = "B25"
Case "EXP2": range1x = "B26"
Case "EXP3": range1x = "B27"
Case "EXP4": range1x = "B28"
Case "EXP5": range1x = "B29"
Case "EXP6": range1x = "B30"
Case "EXP7": range1x = "B31"
Case "EXP8": range1x = "B32"
Case "EXP9": range1x = "B33"
Case "EXP10": range1x = "B34"
Case "EXP11": range1x = "B35"
Case "EXP12": range1x = "B36"
Case "EXP13": range1x = "B37"
Case "EXP14": range1x = "B38"
Case "EXP15": range1x = "B39"
Case "EXP16": range1x = "B40"
Case "EXP17": range1x = "B41"
Case "EXP18": range1x = "B42"
Case "EXP19": range1x = "B43"
Case "EXP20": range1x = "B44"
Case "A1": range1x = "B71"
Case "A2": range1x = "B72"
Case "PS1": range1x = "B83"
Case "Comp1": range1x = "B95"
Case "Comp2": range1x = "B96"
Case "AEDU1": range1x = "B116"
Case "AEDU2": range1x = "B117"
Case "AEXP1": range1x = "B121"
Case "AEXP2": range1x = "B122"
Case "AEXP3": range1x = "B123"
End Select
End If
If range1x <> "" Then
Sheets("SBR").Range("AE3") = _
Sheets("Process Info").Range(range1x).Value
End If
If Target.Address(0, 0) = "AF4" Then
Select Case Target
Case "EDU1": range1s = "B19"
Case "EDU2": range1s = "B20"
Case "EDU3": range1s = "B21"
Case "EDU4": range1s = "B22"
Case "EXP1": range1s = "B25"
Case "EXP2": range1s = "B26"
Case "EXP3": range1s = "B27"
Case "EXP4": range1s = "B28"
Case "EXP5": range1s = "B29"
Case "EXP6": range1s = "B30"
Case "EXP7": range1s = "B31"
Case "EXP8": range1s = "B32"
Case "EXP9": range1s = "B33"
Case "EXP10": range1s = "B34"
Case "EXP11": range1s = "B35"
Case "EXP12": range1s = "B36"
Case "EXP13": range1s = "B37"
Case "EXP14": range1s = "B38"
Case "EXP15": range1s = "B39"
Case "EXP16": range1s = "B40"
Case "EXP17": range1s = "B41"
Case "EXP18": range1s = "B42"
Case "EXP19": ranges1s = "B43"
Case "EXP20": range1s = "B44"
Case "A1": range1s = "B71"
Case "A2": range1s = "B72"
Case "PS1": range1s = "B83"
Case "Comp1": range1s = "B95"
Case "Comp2": range1s = "B96"
Case "AEDU1": range1s = "B116"
Case "AEDU2": range1s = "B117"
Case "AEXP1": range1s = "B121"
Case "AEXP2": range1s = "B122"
Case "AEXP3": range1s = "B123"
End Select
End If
If range1s <> "" Then
Sheets("SBR").Range("AF3") = _
Sheets("Process Info").Range(range1s).Value
End If
If Target.Address(0, 0) = "AG4" Then
Select Case Target
Case "EDU1": range1q = "B19"
Case "EDU2": range1q = "B20"
Case "EDU3": range1q = "B21"
Case "EDU4": range1q = "B22"
Case "EXP1": range1q = "B25"
Case "EXP2": range1q = "B26"
Case "EXP3": range1q = "B27"
Case "EXP4": range1q = "B28"
Case "EXP5": range1q = "B29"
Case "EXP6": range1q = "B30"
Case "EXP7": range1q = "B31"
Case "EXP8": range1q = "B32"
Case "EXP9": range1q = "B33"
Case "EXP10": range1q = "B34"
Case "EXP11": range1q = "B35"
Case "EXP12": range1q = "B36"
Case "EXP13": range1q = "B37"
Case "EXP14": range1q = "B38"
Case "EXP15": range1q = "B39"
Case "EXP16": range1q = "B40"
Case "EXP17": range1q = "B41"
Case "EXP18": range1q = "B42"
Case "EXP19": range1q = "B43"
Case "EXP20": range1q = "B44"
Case "A1": range1q = "B71"
Case "A2": range1q = "B72"
Case "PS1": range1q = "B83"
Case "Comp1": range1q = "B95"
Case "Comp2": range1q = "B96"
Case "AEDU1": range1q = "B116"
Case "AEDU2": range1q = "B117"
Case "AEXP1": range1q = "B121"
Case "AEXP2": range1q = "B122"
Case "AEXP3": range1q = "B123"
End Select
End If
If range1q <> "" Then
Sheets("SBR").Range("AG3") = _
Sheets("Process Info").Range(range1q).Value
End If
If Target.Address(0, 0) = "AH4" Then
Select Case Target
Case "EDU1": range1e = "B19"
Case "EDU2": range1e = "B20"
Case "EDU3": range1e = "B21"
Case "EDU4": range1e = "B22"
Case "EXP1": range1e = "B25"
Case "EXP2": range1e = "B26"
Case "EXP3": range1e = "B27"
Case "EXP4": range1e = "B28"
Case "EXP5": range1e = "B29"
Case "EXP6": range1e = "B30"
Case "EXP7": range1e = "B31"
Case "EXP8": range1e = "B32"
Case "EXP9": range1e = "B33"
Case "EXP10": range1e = "B34"
Case "EXP11": range1e = "B35"
Case "EXP12": range1e = "B36"
Case "EXP13": range1e = "B37"
Case "EXP14": range1e = "B38"
Case "EXP15": range1e = "B39"
Case "EXP16": range1e = "B40"
Case "EXP17": range1e = "B41"
Case "EXP18": range1e = "B42"
Case "EXP19": range1e = "B43"
Case "EXP20": range1e = "B44"
Case "A1": range1e = "B71"
Case "A2": range1e = "B72"
Case "PS1": range1e = "B83"
Case "Comp1": range1e = "B95"
Case "Comp2": range1e = "B96"
Case "AEDU1": range1e = "B116"
Case "AEDU2": range1e = "B117"
Case "AEXP1": range1e = "B121"
Case "AEXP2": range1e = "B122"
Case "AEXP3": range1e = "B123"
End Select
End If
If range1e <> "" Then
Sheets("SBR").Range("AH3") = _
Sheets("Process Info").Range(range1e).Value
End If
If Target.Address(0, 0) = "AI4" Then
Select Case Target
Case "EDU1": range1n = "B19"
Case "EDU2": range1n = "B20"
Case "EDU3": range1n = "B21"
Case "EDU4": range1n = "B22"
Case "EXP1": range1n = "B25"
Case "EXP2": range1n = "B26"
Case "EXP3": range1n = "B27"
Case "EXP4": range1n = "B28"
Case "EXP5": range1n = "B29"
Case "EXP6": range1n = "B30"
Case "EXP7": range1n = "B31"
Case "EXP8": range1n = "B32"
Case "EXP9": range1n = "B33"
Case "EXP10": range1n = "B34"
Case "EXP11": range1n = "B35"
Case "EXP12": range1n = "B36"
Case "EXP13": range1n = "B37"
Case "EXP14": range1n = "B38"
Case "EXP15": range1n = "B39"
Case "EXP16": range1n = "B40"
Case "EXP17": range1n = "B41"
Case "EXP18": range1n = "B42"
Case "EXP19": range1n = "B43"
Case "EXP20": range1n = "B44"
Case "A1": range1n = "B71"
Case "A2": range1n = "B72"
Case "PS1": range1n = "B83"
Case "Comp1": range1n = "B95"
Case "Comp2": range1n = "B96"
Case "AEDU1": range1n = "B116"
Case "AEDU2": range1n = "B117"
Case "AEXP1": range1n = "B121"
Case "AEXP2": range1n = "B122"
Case "AEXP3": range1n = "B123"
End Select
End If
If range1n <> "" Then
Sheets("SBR").Range("AI3") = _
Sheets("Process Info").Range(range1n).Value
End If
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
.
end sub