AW: Reset values
07.09.2005 14:25:04
Tatiana
Macros ist dabei. Es funtionniert auch. Ich will aber, dass wenn ich "AccesEx" von "1" auf 323 vechsele und umgekehrt, dass die Zeile C13 und C15 geleert werden, dass man ist gezwungen sie auszufuehlen.
Danke!
________________________________________________________________________________
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$13" Or Target.Address = "$C$15" Then
If AccesEx = "1" Then
If Cells(13, 3) < 1400 Then
MsgBox "Forget it, Frank! Everybody knows, BS has to be at least 1400"
ChargeEx.ListFillRange = "Tables!F15"
ChargeEx.ListIndex = 0
Exit Sub
End If
If Cells(15, 3) < 1450 Then
MsgBox "Forget it, Frank! Everybody knows, TS has to be at least 1450"
ChargeEx.ListFillRange = "Tables!F15"
ChargeEx.ListIndex = 0
End If
If Cells(13, 3) >= 1400 And Cells(13, 3) < 1500 Then
If Cells(15, 3) >= 1450 And Cells(15, 3) < 1600 Then
ChargeEx.ListFillRange = "Tables!F4"
ChargeEx.ListIndex = 0
End If
End If
If Cells(13, 3) >= 1500 And Cells(13, 3) < 1600 Then
If Cells(15, 3) >= 1600 And Cells(15, 3) < 1650 Then
ChargeEx.ListFillRange = "Tables!F5"
ChargeEx.ListIndex = 0
End If
End If
If Cells(13, 3) >= 1600 And Cells(13, 3) < 1650 Then
If Cells(15, 3) >= 1600 And Cells(15, 3) < 1650 Then
ChargeEx.ListFillRange = "Tables!F6"
ChargeEx.ListIndex = 0
End If
End If
If Cells(13, 3) >= 1600 And Cells(13, 3) < 1650 Then
If Cells(15, 3) >= 1750 And Cells(15, 3) < 2450 Then
ChargeEx.ListFillRange = "Tables!F7"
ChargeEx.ListIndex = 0
End If
End If
If Cells(13, 3) >= 1650 And Cells(13, 3) < 2000 Then
If Cells(15, 3) >= 2450 And Cells(15, 3) < 4000 Then
ChargeEx.ListFillRange = "Tables!F8"
ChargeEx.ListIndex = 0
End If
End If
ElseIf AccesEx = "2" Then
If Cells(13, 3) < 1500 Then
MsgBox "Forget it, Frank! Everybody knows, BS has to be at least 1500"
ChargeEx.ListFillRange = "Tables!F15"
ChargeEx.ListIndex = 0
Exit Sub
End If
If Cells(15, 3) < 1650 Then
MsgBox "Forget it, Frank! Everybody knows, TS has to be at least 1650"
ChargeEx.ListFillRange = "Tables!F15"
ChargeEx.ListIndex = 0
End If
If Cells(13, 3) >= 1500 And Cells(13, 3) < 1600 Then
If Cells(15, 3) >= 1650 And Cells(15, 3) < 1850 Then
ChargeEx.ListFillRange = "Tables!F5"
ChargeEx.ListIndex = 0
End If
End If
If Cells(13, 3) >= 1600 And Cells(13, 3) < 1800 Then
If Cells(15, 3) >= 1650 And Cells(15, 3) < 1850 Then
ChargeEx.ListFillRange = "Tables!F6"
ChargeEx.ListIndex = 0
End If
End If
If Cells(13, 3) >= 1600 And Cells(13, 3) < 1650 Then
If Cells(15, 3) >= 1800 And Cells(15, 3) < 1950 Then
ChargeEx.ListFillRange = "Tables!F7"
ChargeEx.ListIndex = 0
End If
End If
If Cells(13, 3) >= 1650 And Cells(13, 3) < 2500 Then
If Cells(15, 3) >= 2500 And Cells(15, 3) < 2650 Then
ChargeEx.ListFillRange = "Tables!F8"
ChargeEx.ListIndex = 0
End If
End If
End If
End If
End Sub