Beitrag aus SmartTools Excel Weekly
Je nach Benutzereingabe eine bestimmte Anzahl Zellen sperren, Teil 2
Excel 365 2021 2019 2016 2013 2010
Ein Tabellenblatt mit einem Blattschutz zu versehen, damit nur "ungesperrte" Zellen bearbeitet werden können, ist mit Excel kein Problem. Es gibt aber auch Situationen, in denen Sie die Anzahl der gesperrten und ungesperrten Zellen dynamisch von Benutzereingaben abhängig machen möchten. Zum Beispiel: Eingaben sollen nur im Bereich B4:H4 sowie in Zelle B5 möglich sein. Wenn in Zelle B4 ein bestimmter Wert eingegeben wird, sollen Zellen im Bereich C4:H4 gesperrt werden. Die Anzahl der zu sperrenden Zellen soll sich nach dem Wert richten, der in Zelle B5 eingetragen wurde.
Wenn dort zum Beispiel 3 steht, sollen die Zellen C4:E4 (3 Zellen) gesperrt werden; Eingaben in F4:H4 sind weiterhin erlaubt. Wenn in B4 aber 6 steht, werden C4:H4 (6 Zellen) gesperrt, so dass neben B4 keine Eingaben mehr zulässig sind. - Wenn sich die Werte in B4 und B5 ändern, soll die Sperrung natürlich entsprechend angepasst werden.
Zur Lösung benötigen Sie eine VBA-Lösung, in der Sie mit Hilfe einer Ereignisprozedur prüfen, welche Werte in den Zellen B4 und B5 eingegeben worden sind. Wenn in B4 der passende Schlüsselbegriff steht, lassen Sie im Bereich C4:H4 so viele Zellen sperren, wie es durch den Wert in B5 vorgegeben wurde. Die Prozedur dazu haben wir in der letzten Ausgabe vorgestellt. Passen Sie jetzt den VBA-Code an Ihre Arbeitsumgebung an. In den Zeilen
Set rngEingabeWert = ActiveSheet.Range("B4")
Set rngEingabeSperren = ActiveSheet.Range("B5")
Set rngSperrzellen = ActiveSheet.Range("C4:H4")
müssen Sie die Zelladressen mit Ihrem Tabellenblatt abstimmen: "rngEingabeWert" bezieht sich auf die Zelle, in der Sie den Schlüsselbegriff eingeben, der darüber entscheidet, ob überhaupt Zellen gesperrt werden sollen (hier: Zelle B4). "rngEingabeSperren" ist die Zelle, in der Sie die Anzahl der zu sperrenden Zellen eingeben (hier: B5). Und "rngSperrzellen" definiert den Zellbereich, in dem Zellen dynamisch ge- oder entsperrt werden sollen (hier: C4:H4).
In der Codezeile
Case "Fenster", "Trennwand"
geben Sie die Schlüsselbegriffe ein, die das Sperren von Zellen auslösen sollen. Im Beispiel gehen wir davon aus, dass Zellen nur gesperrt werden sollen, wenn in Zelle B4 einer der Begriffe "Fenster" oder "Trennwand" eingegeben wurde. Stattdessen können Sie auch nur einen einzelnen Schlüsselbegriff definieren - etwa:
Case "Hamburg"
oder auch die Eingabe bestimmter Zahlenwerte überwachen:
Case 1, 2, 3
Da Sie in einem geschützten Tabellenblatt normalerweise auch per VBA-Code keine Zellen sperren können, müssen Sie einen kleinen Trick anwenden, damit der Schutz nur für die Benutzeroberfläche gilt, aber nicht für VBA-Anweisungen. Dazu öffnen Sie das Codefenster der Arbeitsmappe, indem Sie mit Strg+R in das Unterfenster mit dem "Projekt-Explorer" wechseln. Darin erkennen Sie am Dateinamen das "VBAProject" der aktuellen Arbeitsmappe. Doppelklicken Sie im Zweig "Microsoft Excel Objekte" auf den Eintrag "DieseArbeitsmappe".
Sie gelangen in das Codefenster mit dem Titel "<Dateiname> - DieseArbeitsmappe". In den Dropdownfeldern am oberen Rand wählen Sie links "Workbook" und rechts "Open" aus.
Das eingefügte Prozedurgerüst erweitern Sie wie folgt:
Private Sub Workbook_Open()
ThisWorkbook.Sheets("Planung").Protect _
Password:="IhrKennwort", _
UserInterfaceOnly:=True
End Sub
Im VBA-Code müssen Sie "Planung" durch den Namen des Tabellenblatts ersetzen, in dem Sie das dynamische Sperren von Zellen realisieren möchten. Außerdem ersetzen Sie "IhrKennwort" durch das Kennwort, mit dem Sie den Blattschutz aktiviert hatten.
Verlassen Sie die Programmierumgebung per Datei-Schließen und zurück zu Microsoft Excel. Speichern Sie die Arbeitsmappe als "Excel-Arbeitsmappe mit Makros (*.xlsm)". Am Ende schließen Sie die Arbeitsmappe.
Beim nächsten Öffnen führt Excel automatisch die "Workbook_Open"-Ereignisprozedur aus, die den Blattschutz für VBA-Anweisungen aufhebt. Ausschlaggebend dafür ist der Parameter "UserInterfaceOnly:=True".
So greift die Ereignisprozedur "Worksheet_Change", die prüft, ob im überwachten Tabellenblatt ein passender Schlüsselbegriff eingegeben wurde. In diesem Fall liest die Prozedur die Zelle aus, in der Sie die Anzahl der zu sperrenden Zellen eingegeben haben, und sperrt entsprechend viele Zellen im Eingabebereich.
Die Sperrung ändert sich dynamisch, sobald Sie neue Werte eingeben. Und wenn Sie den Inhalt der Zelle mit der Anzahl zu sperrender Zellen löschen, sind wieder alle Eingabezelle entsperrt.