2 Bedingungen verknüpfen

Bild

Betrifft: 2 Bedingungen verknüpfen
von: Fritz
Geschrieben am: 23.11.2015 14:43:38

Hallo
Ich möchte 2 Bedingungen verknüpfen:
Select Case Target.Address
Case "$C$30", "$F$30", "$I$30", "$L$30", "$C$39", "$F$39", "$I$39", "$L$39", "$C$48", "$F$48", "$I$48", "$L$48", "$C$57", "$F$57", "$I$57", "$L$57"
Target.Offset(-3, 2).Locked = (Target = "No Option 1+2")
Target.Offset(-2, 2).Locked = (Target = "No Option 1+2")
Case Else
End Select
möchte Target.Offset(-3, 2).Locked + Target.Offset(-2, 2).Locked = (Target = "No Option 1+2")
Wie muss ich das machen, da "+" nicht geht?
Vielen Dank für Eure Hilfe!
Gruss, Fritz

Bild

Betrifft: AW: 2 Bedingungen verknüpfen
von: Rudi Maintaire
Geschrieben am: 23.11.2015 15:02:50
Hallo,
was gefällt dir an deiner Lösung nicht?
Range(Target.Offset(-3, 2), Target.Offset(-2, 2)).Locked = (Target = "No Option 1+2")
oder
Target.Offset(-3, 2).Resize(2).Locked = (Target = "No Option 1+2")
Gruß
Rudi

Bild

Betrifft: Nachtrag
von: Rudi Maintaire
Geschrieben am: 23.11.2015 15:08:28
anstatt mit 16 Adressen würde ich das so machen:

  Select Case Target.Column
    Case 3, 6, 9, 12
      Select Case Target.Row
        Case 30, 39, 48, 57
          Target.Offset(-3, 2).Resize(2).Locked = _
            (Target = "No Option 1+2")
      End Select
  End Select
Gruß
Rudi

Bild

Betrifft: AW: 2 Bedingungen verknüpfen
von: Fritz
Geschrieben am: 23.11.2015 15:45:01
Vielen Dank Rudi
hätte wohl besser alles angegeben (nicht nur einen Teil...)
habe alles angepasst, erhalte nun die fehlermeldung:
"Fehler beim Kompilieren.:
Falsche Anzahl von Argumenten oder ungültige Eigenschaftenzuweisung."
Select Case Target.Address
Case "$C$30", "$F$30", "$I$30", "$L$30", "$C$39", "$F$39", "$I$39", "$L$39", "$C$48", "$F$48", "$I$48", "$L$48", "$C$57", "$F$57", "$I$57", "$L$57"
Range(Target.Offset(-3, 2), Target.Offset(-2, 2)).Locked = (Target = "No Option 1+2")
Case Else
End Select
Select Case Target.Address
Case "$C$30", "$F$30", "$I$30", "$L$30", "$C$39", "$F$39", "$I$39", "$L$39", "$C$48", "$F$48", "$I$48", "$L$48", "$C$57", "$F$57", "$I$57", "$L$57"
Range(Target.Offset(-3, 2), Target.Offset(-1, 2)).Locked = (Target = "No Option 1+3")
Case Else
End Select
Select Case Target.Address
Case "$C$30", "$F$30", "$I$30", "$L$30", "$C$39", "$F$39", "$I$39", "$L$39", "$C$48", "$F$48", "$I$48", "$L$48", "$C$57", "$F$57", "$I$57", "$L$57"
Range(Target.Offset(-2, 2), Target.Offset(-1, 2)).Locked = (Target = "No Option 2+3")
Case Else
End Select

Select Case Target.Address
Case "$C$30", "$F$30", "$I$30", "$L$30", "$C$39", "$F$39", "$I$39", "$L$39", "$C$48", "$F$48", "$I$48", "$L$48", "$C$57", "$F$57", "$I$57", "$L$57"
Range(Target.Offset(-3, 2), Target.Offset(-2, 2), Target.Offset(-1, 2)).Locked = (Target = "No Option")
Case Else
End Select

Select Case Target.Address
Case "$C$65", "$F$65", "$I$65", "$L$65"
Range(Target.Offset(-2, 2), Target.Offset(-1, 2)).Locked = (Target = "No Option")
Case Else
End Select
Gruss, Fritz

Bild

Betrifft: AW: 2 Bedingungen verknüpfen
von: Rudi Maintaire
Geschrieben am: 23.11.2015 15:57:46
Hallo,
teste mal

  Select Case Target.Column
    Case 3, 6, 9, 12
      Select Case Target.Row
      Case 30, 39, 48, 57
        Range(Target.Offset(-3, 2), Target.Offset(-2, 2)).Locked = _
          Target = "No Option 1+2" Or _
          Target = "No Option 1+3" Or _
          Target = "No Option 2+3" Or _
          Target = "No Option"
      Case 65
        Range(Target.Offset(-2, 2), Target.Offset(-1, 2)).Locked = _
          Target = "No Option"
    End Select
  End Select
Gruß
Rudi

Bild

Betrifft: AW: 2 Bedingungen verknüpfen
von: Fritz
Geschrieben am: 23.11.2015 16:10:16
Besten Dank Rudi!
es klappt noch nicht ganz..
es werden zum Teil noch falsche Zellen blockiert
aber ich werde noch etwas weiter üben
Gruss
Fritz

Bild

Betrifft: AW: 2 Bedingungen verknüpfen
von: Fritz
Geschrieben am: 26.11.2015 12:06:31
Hallo zusammen,
hab fleissig weiter geübt, aber ich schaffe es einfach nicht, dass die entsprechenden Zellen bei entsprechenden Fällen geblockt werden...
Zelle E27 ist Option 1
Zelle E28 ist Option 2
Zelle E29 ist Option 3
verfügbare Wahl (Fälle) in Zelle C30:
"No Option 1"
"No Option 2"
"No Option 3"
"No Option 1+2"
"No Option 1+3"
"No Option 2+3"
"No Option"
Select Case Target.Address
Case "$C$30", "$F$30", "$I$30", "$L$30", "$C$39", "$F$39", "$I$39", "$L$39", "$C$48", "$F$48", "$I$48", "$L$48", "$C$57", "$F$57", "$I$57", "$L$57"
Target.Offset(-3, 2).Locked = (Target = "No Option 1")
Case Else
End Select
Select Case Target.Address
Case "$C$30", "$F$30", "$I$30", "$L$30", "$C$39", "$F$39", "$I$39", "$L$39", "$C$48", "$F$48", "$I$48", "$L$48", "$C$57", "$F$57", "$I$57", "$L$57"
Target.Offset(-2, 2).Locked = (Target = "No Option 2")
Case Else
End Select
Select Case Target.Address
Case "$C$30", "$F$30", "$I$30", "$L$30", "$C$39", "$F$39", "$I$39", "$L$39", "$C$48", "$F$48", "$I$48", "$L$48", "$C$57", "$F$57", "$I$57", "$L$57"
Target.Offset(-1, 2).Locked = (Target = "No Option 3")
Case Else
End Select

Select Case Target.Address
Case "$C$65", "$F$65", "$I$65", "$L$65"
Target.Offset(-2, 2).Locked = (Target = "No Option 1")
Case Else
End Select

Select Case Target.Address
Case "$C$65", "$F$65", "$I$65", "$L$65"
Target.Offset(-1, 2).Locked = (Target = "No Option 2")
Case Else
End Select
Select Case Target.Address
Case "$C$30", "$F$30", "$I$30", "$L$30", "$C$39", "$F$39", "$I$39", "$L$39", "$C$48", "$F$48", "$I$48", "$L$48", "$C$57", "$F$57", "$I$57", "$L$57"
Target.Offset(-3, 2).Locked = (Target = "No Option 1+2")
Case Else
End Select


Select Case Target.Address
Case "$C$30", "$F$30", "$I$30", "$L$30", "$C$39", "$F$39", "$I$39", "$L$39", "$C$48", "$F$48", "$I$48", "$L$48", "$C$57", "$F$57", "$I$57", "$L$57"
Target.Offset(-2, 2).Locked = (Target = "No Option 1+2")
Case Else
End Select

Select Case Target.Address
Case "$C$30", "$F$30", "$I$30", "$L$30", "$C$39", "$F$39", "$I$39", "$L$39", "$C$48", "$F$48", "$I$48", "$L$48", "$C$57", "$F$57", "$I$57", "$L$57"
Target.Offset(-3, 2).Locked = (Target = "No Option 1+3")
Case Else
End Select

Select Case Target.Address
Case "$C$30", "$F$30", "$I$30", "$L$30", "$C$39", "$F$39", "$I$39", "$L$39", "$C$48", "$F$48", "$I$48", "$L$48", "$C$57", "$F$57", "$I$57", "$L$57"
Target.Offset(-1, 2).Locked = (Target = "No Option 1+3")
Case Else
End Select

Select Case Target.Address
Case "$C$30", "$F$30", "$I$30", "$L$30", "$C$39", "$F$39", "$I$39", "$L$39", "$C$48", "$F$48", "$I$48", "$L$48", "$C$57", "$F$57", "$I$57", "$L$57"
Target.Offset(-2, 2).Locked = (Target = "No Option 2+3")
Case Else
End Select


Select Case Target.Address
Case "$C$30", "$F$30", "$I$30", "$L$30", "$C$39", "$F$39", "$I$39", "$L$39", "$C$48", "$F$48", "$I$48", "$L$48", "$C$57", "$F$57", "$I$57", "$L$57"
Target.Offset(-1, 2).Locked = (Target = "No Option 2+3")
Case Else
End Select


Select Case Target.Address
Case "$C$30", "$F$30", "$I$30", "$L$30", "$C$39", "$F$39", "$I$39", "$L$39", "$C$48", "$F$48", "$I$48", "$L$48", "$C$57", "$F$57", "$I$57", "$L$57"
Target.Offset(-3, 2).Locked = (Target = "No Option")
Case Else
End Select


Select Case Target.Address
Case "$C$30", "$F$30", "$I$30", "$L$30", "$C$39", "$F$39", "$I$39", "$L$39", "$C$48", "$F$48", "$I$48", "$L$48", "$C$57", "$F$57", "$I$57", "$L$57"
Target.Offset(-2, 2).Locked = (Target = "No Option")
Case Else
End Select


Select Case Target.Address
Case "$C$30", "$F$30", "$I$30", "$L$30", "$C$39", "$F$39", "$I$39", "$L$39", "$C$48", "$F$48", "$I$48", "$L$48", "$C$57", "$F$57", "$I$57", "$L$57"
Target.Offset(-1, 2).Locked = (Target = "No Option")
Case Else
End Select


Select Case Target.Address
Case "$C$65", "$F$65", "$I$65", "$L$65"
Target.Offset(-2, 2).Locked = (Target = "No Option")
Case Else
End Select


Select Case Target.Address
Case "$C$65", "$F$65", "$I$65", "$L$65"
Target.Offset(-1, 2).Locked = (Target = "No Option")
Case Else
End Select
wenn ich nur folgende Einträge stehen lasse, dann werden die Zellen E27, E28, bzw. E29 bei entsprechender Wahl von "No Option 1", "No Option 2", bzw. "No Option 3", wie sie sollen auch gesperrt:
Select Case Target.Address
Case "$C$30", "$F$30", "$I$30", "$L$30", "$C$39", "$F$39", "$I$39", "$L$39", "$C$48", "$F$48", "$I$48", "$L$48", "$C$57", "$F$57", "$I$57", "$L$57"
Target.Offset(-3, 2).Locked = (Target = "No Option 1")
Case Else
End Select
Select Case Target.Address
Case "$C$30", "$F$30", "$I$30", "$L$30", "$C$39", "$F$39", "$I$39", "$L$39", "$C$48", "$F$48", "$I$48", "$L$48", "$C$57", "$F$57", "$I$57", "$L$57"
Target.Offset(-2, 2).Locked = (Target = "No Option 2")
Case Else
End Select
Select Case Target.Address
Case "$C$30", "$F$30", "$I$30", "$L$30", "$C$39", "$F$39", "$I$39", "$L$39", "$C$48", "$F$48", "$I$48", "$L$48", "$C$57", "$F$57", "$I$57", "$L$57"
Target.Offset(-1, 2).Locked = (Target = "No Option 3")
Case Else
End Select

Select Case Target.Address
Case "$C$65", "$F$65", "$I$65", "$L$65"
Target.Offset(-2, 2).Locked = (Target = "No Option 1")
Case Else
End Select

Select Case Target.Address
Case "$C$65", "$F$65", "$I$65", "$L$65"
Target.Offset(-1, 2).Locked = (Target = "No Option 2")
Case Else
End Select
Bin sehr dankbar um jegliche Hilfe!
Gruss, Fritz

Bild

Betrifft: AW: 2 Bedingungen verknüpfen
von: Rudi Maintaire
Geschrieben am: 26.11.2015 13:04:50
hallo,
du hast ein Ablaufproblem.
Wenn hier

Select Case Target.Address
 Case "$C$30", "$F$30", "$I$30", "$L$30", "$C$39", "$F$39", "$I$39", "$L$39", "$C$48", "$F$48",  _
"$I$48", "$L$48", "$C$57", "$F$57", "$I$57", "$L$57"
 Target.Offset(-3, 2).Locked = (Target = "No Option 1")
 Case Else
 End Select

gesperrt wird, wird die Sperre hier
Select Case Target.Address
 Case "$C$30", "$F$30", "$I$30", "$L$30", "$C$39", "$F$39", "$I$39", "$L$39", "$C$48", "$F$48",  _
"$I$48", "$L$48", "$C$57", "$F$57", "$I$57", "$L$57"
 Target.Offset(-3, 2).Locked = (Target = "No Option 1+2")
 Case Else
 End Select

wieder aufgehoben, da nicht beides WAHR sein kann.
Das ganze Konzept ist nix.
und zig-Mal die Adresse abzufragen ist auch nicht nötig.
Gruß
Rudi

Bild

Betrifft: AW: 2 Bedingungen verknüpfen
von: Fritz
Geschrieben am: 26.11.2015 13:40:06
Hallo Rudi,
ok, danke! ich geb's auf, bin zu wenig versiert...
Gruss, Fritz

 Bild

Beiträge aus den Excel-Beispielen zum Thema "2 Bedingungen verknüpfen"