Wie kann das Löschen oder Ändern referenzierter Werte in Excel verhindert werden?

398
Alex R

Ich habe ein Excel-Arbeitsblatt, das eindeutige IDs enthält, und ein weiteres Arbeitsblatt, das Einträge enthält, die (per Data Validation - List) die IDs aus dem ersten Arbeitsblatt referenzieren.

Ich habe die Datenvalidierung im 2. Blatt aktiviert, wodurch erfolgreich verhindert wird, dass Werte in die ID-Spalte eingegeben werden, wenn sie nicht im 1. Blatt vorhanden sind.

Es gibt jedoch einen Fehler: Excel lässt immer noch zu, dass ein ungültiger Wert in die ID-Spalte im 2. Blatt eingefügt wird, indem der ID-Wert nach Eingabe in das 2. Blatt im 1. Blatt entfernt oder geändert wird.

Wie kann ich zwingen, dass Excel das Ändern oder Entfernen des ID-Werts im 1. Blatt nicht zulässt, solange ein entsprechender ID-Wert in der validierten Spalte im 2. Blatt vorhanden ist?

0
Sie könnten das erste Blatt schützen, so dass Änderungen * daran * nicht zulässig sind cybernetic.nomad vor 5 Jahren 0
Das geht zu weit. Ich muss eine große Anzahl von Aktualisierungen für beide Arbeitsblätter vornehmen, und ich möchte vor dem versehentlichen Löschen eines Werts geschützt werden, der auf dem anderen Arbeitsblatt erforderlich ist. Alex R vor 5 Jahren 0
Sie benötigen wahrscheinlich ein bisschen VBA-Code, um die Listen zu überwachen. Vor dem Ändern einer Zelle im festgelegten Bereich kann der andere Bereich daraufhin überprüft werden, ob er vorhanden ist. Abhängig von den Ergebnissen können Sie zulassen, dass der Wert die beabsichtigte Aktion ändert oder verhindert. Leider bin ich ein extrem schwacher VBA-Programmierer. Wenn Sie es googeln, finden Sie möglicherweise einen Beispielcode. Sie können Stackoverflow auch um Hilfe bei der Bearbeitung Ihres Codes bitten, wenn Sie Probleme damit haben. Vielleicht haben Sie Glück und jemand wird hier einen Code als Antwort posten. Forward Ed vor 5 Jahren 1
Hat eine Flagge, die Ihnen sagt, dass ein Wert in Tabelle 1 für Sie ausreichend ist? Wenn ja, würde eine Hilfszeile (oder eine bedingte Formatierung) mit einem COUNTIF die Arbeit erledigen. cybernetic.nomad vor 5 Jahren 1
@AlexR, ich bin mir sicher, dass Sie Datenbereich aus Blatt1 mit IDs verwenden, um als Quelle in Blatt 2 zu validieren. Wenn Sie in diesem Fall den Datenbereich in Blatt1 sperren, können Sie dann tun, was Sie wollen! Rajesh S vor 5 Jahren 0

3 Antworten auf die Frage

0
Rob Gale

Wie @ForwardEd hervorgehoben hat, erfordert dies einige VBA. Der folgende Code wird ausgelöst, wenn in Ihrer Liste eindeutiger IDs etwas geändert wird, und überprüfen Sie, ob der alte Wert in der anderen Liste (Ihrem zweiten Blatt) vorhanden war. Wenn es existiert, wird die Änderung rückgängig gemacht.

Private Sub Worksheet_Change(ByVal Target As Range) Dim vOld As Variant Dim vNew As Variant  If Not Intersect([ProductListItemID], Target) Is Nothing Then vNew = Target.Value Application.EnableEvents = False Application.Undo vOld = Target.Value If WorksheetFunction.CountIf([OrdersItemID], Target) > 0 Then MsgBox "Change disallowed" Else Target.Value = vNew 'MsgBox "Change OK" End If Application.EnableEvents = True End If End Sub

Dies sollte im Code für Sheet1 stehen. Ich gehe von zwei benannten Bereichen aus:

ProductListItemID (List of items on Sheet1 to be protected) OrdersItemID (List of ItemID's in 2nd sheet)

Ich habe beide Bereiche auf einem Blatt zusammengefasst: Arbeitsblatt-Beispiel

Erläuterung: Wenn in Sheet1 eine Änderung vorgenommen wird, prüft das Makro, ob es sich im Bereich ProductListItemID befindet. Wenn ja, nimmt es den geänderten Wert (vNew), macht ein Rückgängigmachen und den vorherigen Wert (vOld). Anschließend wird geprüft, ob der vOld-Wert im OrdersItemID-Bereich vorhanden ist. Wenn dies der Fall war, bleibt die Zelle auf dem alten Wert, andernfalls wird der neue Wert wiederhergestellt.

Der Code funktioniert gut, hat aber wenig Mühe, wenn ich eine neue ID in Sheet1 eingebe und dieselbe ID in Shee2 hinzufüge. Danach verhindert dieser Code, dass die neu eingegebene ID in Sheet1 geändert oder gelöscht wird. Erlaubt mir jedoch, die neu eingegebene ID in Sheet1 zu duplizieren. Ich denke, technisch sollte es nie passieren, dass cozz Sheet 1 eine eindeutige Liste trägt. Rajesh S vor 5 Jahren 0
Die von Ihnen verwendete Rückgängig-Methode ist ein bisschen Anti-Pattern. Dies bedeutet, dass Sie Aktivitäten rückgängig machen und dann wiederholen, wenn dies nicht erforderlich ist. Ich habe gerade dieses Anti-Pattern überprüft, bei dem ich glaube (ich kann nicht bestätigen), dass es das Arbeitsblatt beschädigt hat. AJD vor 5 Jahren 0
0
AJD

Eine einfache Lösung ist die Verwendung der Worksheet_SelectionChangeVeranstaltung. Ich werde hier ein bisschen frech sein und das Bild verwenden, das Rob Gale vorbereitet hat. Ich habe dieses Bild in dieser Antwort reproduziert, falls Robs Antwort etwas passiert.

Mit dem folgenden Code sollten Sie die Zellen sperren, wenn in Blatt 2 eine entsprechende Element-ID gefunden wird. Bei der nächsten Auswahl einer Zelle sollte sie die Zellen für zukünftige Bearbeitungen entsperren. 

Private Sub Worksheet_SelectionChange(ByVal Target As Range) Dim tRangeToProtect as Range Set tRangeToProtect = Intersect([ProductListItemID], Target)  If Not tRangeToProtect Is Nothing Then Dim tCell as Range Dim tItemFound as Boolean tItemFound = False  For Each tCell in tRangeToProtect ' "Target" can be multiple cells. Must always hand this. tItemFound = tItemFound OR WorksheetFunction.CountIf([OrdersItemID],tCell) > 0 Next tCell if tItemFound Then  tRangetoProtect.Locked = True Me.Protect UserInterfaceOnly = True ' Use this in conjunction with worksheet.Protect UserInterfaceOnly := True Else [ProductList].Locked = False ' Open this up for future checks and editing - remove any existing locks End If End Sub

Dies ist nur eine grobe Lösung - könnte je nach Geschäftskontext verfeinert werden. Ich habe dies auch nicht getestet, daher kann Ihre Laufleistung variieren.

Ich denke, das ist eine schöne VBA-Lösung. Ich habe die Verwendung von "Worksheet_SelectionChange" vermieden, da ich in der Vergangenheit Probleme hatte, bei denen der Benutzer mehrere Zellen eingefügt hat. Aufgrund der Tatsache, dass Sie die Seite sperren, scheint Ihre Lösung dieses Problem zu vermeiden. Eine kleinere Korrektur: `Me.Protect UserInterfaceOnly = True` sollte` Me.Protect UserInterfaceOnly: = True` sein Rob Gale vor 5 Jahren 0
@ RobGale: Danke, das ":" wurde behoben. Ja, Drag & Drop könnte ein Problem sein, aber ich habe noch keinen vollständigen Test durchgeführt. Der Code, den ich in meinem normalen Leben überarbeitet habe, verwendet eine globale Variable, um den Schnitt- / Kopiermodus zu verfolgen, ein _Change -Ereignis, um Validierungsfehler zu überprüfen (und dann rückgängig zu machen), und ein `_SelectionChange`, um das Ausschneiden nur auf geschützten Blättern zu verhindern ( durch Abbrechen der Schnittaktion, nicht rückgängig machen). AJD vor 5 Jahren 0
0
Rob Gale

Hier ist eine Nicht-VBA-Alternative, die die bedingte Formatierung verwendet. Es ist darauf angewiesen, dass der Benutzer weiß, was zu tun ist, aber wenn Sie es so offensichtlich machen, dass ein Fehler vorliegt, sollten Sie in Ordnung sein (abhängig von den Benutzern).

1) Fügen Sie eine Formel hinzu, um Fehler zu erkennen. Geben Sie in Sheet1 Zelle B2 ein: =IF(OR(MAX(COUNTIF(ProductListItemId,ProductListItemId))>1,MIN(COUNTIF(ProductListItemId,OrdersItemID))=0),"ERROR","ok") Dies ist eine Matrixformel. Sie müssen also Strg-Umschalt-Eingabe anstelle von Enter drücken. Die Formel wird von {} umgeben.

2) Bedingte Formatierung: conditional formatting Wenn Sie die bedingte Formatierung auf die gesamte Seite oder einen ausreichend großen Bereich anwenden, sollte dies ausreichen, um den Benutzer in seinen Spuren zu stoppen. Sie können Anweisungen hinzufügen, um die Änderungen rückgängig zu machen, und es erneut versuchen.

Ich möchte darauf hinweisen, dass die Formel in B2 auch nach Duplikaten in Ihrer Produktliste (Artikel auf Blatt 1) ​​sucht.

Ergebnis:

enter image description here

Verwandte Probleme