Skript verkürzen / schneller machen
01.11.2003 11:55:30
Daniel
Ich habe hier ein riesiges Skript. Ich bin mir allerdings ziemlich sicher, dass man dieses irgendwie verkürzen kann, nur weiss ich nicht wie.
Vielen Dank!!!
Daniel.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub ' falls mehrere Zellen mit einmal gefüllt werden
'Sponsorship
If Target.Address(0, 0) = "F29" Then
If Target = "Nein" Then Rows("30:70").Hidden = True
If Target = "Ja" Then Rows("30:70").Hidden = False
If Target = "Ja" Then Range("F34").Value = "Ja"
If Target = "Ja" Then Range("F41").Value = "Ja"
If Target = "Ja" Then Range("F49").Value = "6"
If Target = "Ja" Then Range("F48").Value = "Ja"
If Target = "Ja" Then Range("F62").Value = "Ja"
ElseIf Target.Address(0, 0) = "F34" Then 'Indikativ
If Target = "Nein" Then Rows("35:38").Hidden = True
If Target = "Ja" Then Rows("35:38").Hidden = False
ElseIf Target.Address(0, 0) = "F41" Then 'Abdikativ
If Target = "Nein" Then Rows("42:45").Hidden = True
If Target = "Ja" Then Rows("42:45").Hidden = False
ElseIf Target.Address(0, 0) = "F48" Then 'Reminder
If Target = "Nein" Then Rows("49:58").Hidden = True
If Target = "Ja" Then Range("F49").Value = "6"
If Target = "Ja" Then Rows("49:58").Hidden = False
ElseIf Target.Address(0, 0) = "F49" Then
If Target = "1" Then Rows("53:58").Hidden = True
If Target = "2" Then Rows("53:58").Hidden = True
If Target = "3" Then Rows("53:58").Hidden = False
If Target = "3" Then Rows("56:58").Hidden = True
If Target = "4" Then Rows("53:58").Hidden = False
If Target = "4" Then Rows("56:58").Hidden = True
If Target = "5" Then Rows("53:58").Hidden = False
If Target = "6" Then Rows("53:58").Hidden = False
ElseIf Target.Address(0, 0) = "F62" Then 'Promotrailer
If Target = "Nein" Then Rows("63:70").Hidden = True
If Target = "Ja" Then Rows("63:70").Hidden = False
ElseIf Target.Address(0, 0) = "F75" Then 'Exklusivinseln
If Target = "Nein" Then Rows("76:97").Hidden = True
If Target = "Ja" Then Rows("76:97").Hidden = False
If Target = "Ja" Then Range("F79").Value = "Ja"
If Target = "Ja" Then Range("F93").Value = "Ja"
If Target = "Ja" Then Range("F80").Value = "6"
ElseIf Target.Address(0, 0) = "F79" Then 'Preminder
If Target = "Nein" Then Rows("80:89").Hidden = True
If Target = "Ja" Then Range("F80").Value = "6"
If Target = "Ja" Then Rows("80:89").Hidden = False
ElseIf Target.Address(0, 0) = "F80" Then
If Target = "1" Then Rows("84:89").Hidden = True
If Target = "2" Then Rows("84:89").Hidden = True
If Target = "3" Then Rows("84:89").Hidden = False
If Target = "3" Then Rows("87:89").Hidden = True
If Target = "4" Then Rows("84:89").Hidden = False
If Target = "4" Then Rows("87:89").Hidden = True
If Target = "5" Then Rows("84:89").Hidden = False
If Target = "6" Then Rows("84:89").Hidden = False
ElseIf Target.Address(0, 0) = "F93" Then 'Abspannspot
If Target = "Nein" Then Rows("94:96").Hidden = True
If Target = "Ja" Then Rows("94:96").Hidden = False
ElseIf Target.Address(0, 0) = "F101" Then 'Infomercials
If Target = "Nein" Then Rows("102:147").Hidden = True
If Target = "Ja" Then Range("F102").Value = "15"
If Target = "Ja" Then Rows("102:147").Hidden = False
ElseIf Target.Address(0, 0) = "F102" Then
If Target = "1" Then Rows("106:147").Hidden = True
If Target = "2" Then Rows("106:147").Hidden = False
If Target = "2" Then Rows("109:147").Hidden = True
If Target = "3" Then Rows("106:147").Hidden = False
If Target = "3" Then Rows("112:147").Hidden = True
If Target = "4" Then Rows("106:147").Hidden = False
If Target = "4" Then Rows("115:147").Hidden = True
If Target = "5" Then Rows("106:147").Hidden = False
If Target = "5" Then Rows("118:147").Hidden = True
If Target = "6" Then Rows("106:147").Hidden = False
If Target = "6" Then Rows("121:147").Hidden = True
If Target = "7" Then Rows("106:147").Hidden = False
If Target = "7" Then Rows("124:147").Hidden = True
If Target = "8" Then Rows("106:147").Hidden = False
If Target = "8" Then Rows("127:147").Hidden = True
If Target = "9" Then Rows("106:147").Hidden = False
If Target = "9" Then Rows("130:147").Hidden = True
If Target = "10" Then Rows("106:147").Hidden = False
If Target = "10" Then Rows("133:147").Hidden = True
If Target = "11" Then Rows("106:147").Hidden = False
If Target = "11" Then Rows("136:147").Hidden = True
If Target = "12" Then Rows("106:147").Hidden = False
If Target = "12" Then Rows("139:147").Hidden = True
If Target = "13" Then Rows("106:147").Hidden = False
If Target = "13" Then Rows("142:147").Hidden = True
If Target = "14" Then Rows("106:147").Hidden = False
If Target = "14" Then Rows("145:147").Hidden = True
If Target = "15" Then Rows("106:147").Hidden = False
ElseIf Target.Address(0, 0) = "F15" Then ' Start Kalender
If Range("F15").Value = "Automatisch" Then
Range("BO15:BR26").Copy _
Destination:=Range("X15:AA26")
ElseIf Range("F15").Value = "Manuell" Then
Range("X15:Y15").Select
MsgBox ("Bitte geben Sie die Sendungen pro Monat manuell ein")
'Range("X15:AA26").ClearContents
Range("BT15:BW26").Copy _
Destination:=Range("X15:AA26")
ElseIf Range("F15").Value <> "" Then
Range("F15").Select
MsgBox ("Automatisch oder Manuell wählen")
Range("F15").ClearContents
End If
ElseIf Target.Address(0, 0) = "F22" Then ' Preisgruppe Samstag
If Range("F22").Value = "Ja" Then
Range("BO36:BV36").Copy _
Destination:=Range("T36:AA36") ' Indikativ
Range("BO36:BV36").Copy _
Destination:=Range("T43:AA43") ' Abdikativ
Range("BO36:BV36").Copy _
Destination:=Range("K53:R53") ' Reminder 1
Range("BO36:BV36").Copy _
Destination:=Range("T53:AA53") ' Reminder 2
Range("BO36:BV36").Copy _
Destination:=Range("K58:R58") ' Reminder 3
Range("BO36:BV36").Copy _
Destination:=Range("T58:AA58") ' Reminder 4
Range("BO36:BV36").Copy _
Destination:=Range("K63:R63") ' Reminder 5
Range("BO36:BV36").Copy _
Destination:=Range("T63:AA63") ' Reminder 6
ElseIf Range("F22").Value = "Nein" Then
Range("BX36:CE36").Copy _
Destination:=Range("T36:AA36") ' Indikativ
Range("BX36:CE36").Copy _
Destination:=Range("T43:AA43") ' Abdikativ
Range("BX36:CE36").Copy _
Destination:=Range("K53:R53") ' Reminder 1
Range("BX36:CE36").Copy _
Destination:=Range("T53:AA53") ' Reminder 2
Range("BX36:CE36").Copy _
Destination:=Range("K58:R58") ' Reminder 3
Range("BX36:CE36").Copy _
Destination:=Range("T58:AA58") ' Reminder 4
Range("BX36:CE36").Copy _
Destination:=Range("K63:R63") ' Reminder 5
Range("BX36:CE36").Copy _
Destination:=Range("T63:AA63") ' Reminder 6
ElseIf Range("F22").Value <> "" Then
Range("F22").Select
MsgBox ("Fehler")
Range("F22").ClearContents
End If
ElseIf Target.Address(0, 0) = "F23" Then ' Preisgruppe Sonntag
If Range("F23").Value = "Ja" Then
Range("BO37:BV37").Copy _
Destination:=Range("T37:AA37")
Range("BO37:BV37").Copy _
Destination:=Range("T44:AA44")
Range("BO37:BV37").Copy _
Destination:=Range("K54:R54") ' Reminder 1
Range("BO37:BV37").Copy _
Destination:=Range("T54:AA54") ' Reminder 2
Range("BO37:BV37").Copy _
Destination:=Range("K59:R59") ' Reminder 3
Range("BO37:BV37").Copy _
Destination:=Range("T59:AA59") ' Reminder 4
Range("BO37:BV37").Copy _
Destination:=Range("K64:R64") ' Reminder 5
Range("BO37:BV37").Copy _
Destination:=Range("T64:AA64") ' Reminder 6
ElseIf Range("F23").Value = "Nein" Then
Range("BX37:CE37").Copy _
Destination:=Range("T37:AA37")
Range("BX37:CE37").Copy _
Destination:=Range("T44:AA44")
Range("BX37:CE37").Copy _
Destination:=Range("K54:R54") ' Reminder 1
Range("BX37:CE37").Copy _
Destination:=Range("T54:AA54") ' Reminder 2
Range("BX37:CE37").Copy _
Destination:=Range("K59:R59") ' Reminder 3
Range("BX37:CE37").Copy _
Destination:=Range("T59:AA59") ' Reminder 4
Range("BX37:CE37").Copy _
Destination:=Range("K64:R64") ' Reminder 5
Range("BX37:CE37").Copy _
Destination:=Range("T64:AA64") ' Reminder 6
ElseIf Range("F23").Value <> "" Then
Range("F23").Select
MsgBox ("Fehler")
Range("F23").ClearContents
End If
End If
End Sub