Live-Forum - Die aktuellen Beiträge
Datum
Titel
26.04.2024 17:18:47
Große Datenmenge in komplexes Diagramm verpacken
26.04.2024 16:22:47
Aktualisierung Verknüpfung bei geschlossener Quelldatei
26.04.2024 16:05:28
PivotChart-Stapeldiagramm erstellen
26.04.2024 15:34:42
Überlagernde Textbox-Ansicht
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1460to1464
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1460to1464
1460to1464
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

2 Bedingungen verknüpfen

2 Bedingungen verknüpfen
23.11.2015 14:43:38
Fritz
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

8
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: 2 Bedingungen verknüpfen
23.11.2015 15:02:50
Rudi
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

Nachtrag
23.11.2015 15:08:28
Rudi
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

AW: 2 Bedingungen verknüpfen
23.11.2015 15:45:01
Fritz
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

Anzeige
AW: 2 Bedingungen verknüpfen
23.11.2015 15:57:46
Rudi
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

AW: 2 Bedingungen verknüpfen
23.11.2015 16:10:16
Fritz
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

Anzeige
AW: 2 Bedingungen verknüpfen
26.11.2015 12:06:31
Fritz
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

Anzeige
AW: 2 Bedingungen verknüpfen
26.11.2015 13:04:50
Rudi
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

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige