Beitrag aus SmartTools Excel Weekly
Je nach Benutzereingabe eine bestimmte Anzahl Zellen sperren, Teil 1
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, die wir in diesem Tipp vorstellen.
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.
Zunächst ein Hinweis zum generellen Aufbau des Tabellenblatts: Anfangs sollten Sie die Sperrung für den Bereich B4:H4 sowie für Zelle B5 aufheben. Dazu markieren Sie die Zellen und öffnen mit Strg + 1 das Dialogfenster Zellen formatieren. Auf der Registerkarte Schutz entfernen Sie dann den Haken im Kontrollkästchen Gesperrt. Zur Bestätigung klicken Sie auf Ok.
Anschließend schalten Sie den Blattschutz ein, indem Sie Überprüfen-Änderungen-Blatt schützen anwählen. Nach Eingabe eines Kennworts lassen sich nur noch die ungesperrten Zellen des aktuellen Tabellenblatts bearbeiten - in Ihrem Fall also B4:H4 sowie B5.
Um nun dynamisch eine bestimmte Anzahl an Zellen im Bereich C4:H4 zu sperren bzw. zu "entsperren", erstellen Sie VBA-Code in der Ereignisprozedur "Worksheet_Change". Diese Prozedur führt Excel automatisch aus, sobald sich Werte im aktuellen Tabellenblatt ändern. Zum Anlegen des VBA-Codes führen Sie folgende Schritte aus:
- Klicken Sie mit der rechten Maustaste auf das Blattregister des Tabellenblatts, in dem Sie das dynamische Sperren der Eingabezellen einrichten wollen. Im aufklappenden Kontextmenü wählen Sie Code anzeigen an.
- Sie gelangen automatisch in die Entwicklungsumgebung von Excel, in der das Codefenster des aktuellen Tabellenblatts angezeigt wird. Am oberen Rand dieses Fensters sehen Sie zwei Dropdownfelder. Wählen Sie im linken Feld "Worksheet" und im rechten Feld "Change" aus.
- Daraufhin fügt Excel ein Prozedurgerüst für das "Worksheet_Change"-Ereignis ein. Erweitern Sie den Programmcode folgendermaßen:
Private Sub Worksheet_Change( _
ByVal Target As Range)
Dim rngEingabeWert As Range
Dim rngEingabeSperren As Range
Dim rngSperrzellen As Range
Dim lngAnzahlSperren As Long
On Error GoTo WkSh_Change_Error
Set rngEingabeWert = ActiveSheet.Range("B4")
Set rngEingabeSperren = ActiveSheet.Range("B5")
Set rngSperrzellen = ActiveSheet.Range("C4:H4")
If Not Intersect(Target, _
Range(rngEingabeWert, rngEingabeSperren)) _
Is Nothing Then
rngSperrzellen.Locked = False
Select Case rngEingabeWert.Value
Case "Fenster", "Trennwand"
lngAnzahlSperren = IIf( _
IsEmpty(rngEingabeSperren.Value), 0, _
rngEingabeSperren.Value)
If lngAnzahlSperren > 0 Then
rngSperrzellen.Range( _
Cells(1, 1), _
Cells(1, lngAnzahlSperren)) _
.Locked = True
End If
End Select
End If
WkSh_Change_End:
Set rngSperrzellen = Nothing
Set rngEingabeSperren = Nothing
Set rngEingabeWert = Nothing
Exit Sub
WkSh_Change_Error:
MsgBox Prompt:="Zellen können nicht " & _
"gesperrt/entsperrt werden." & vbCrLf & _
"Fehlernr. " & Err.Number & ": " & _
Err.Description
Resume WkSh_Change_End
End Sub
Auf den Feinschliff, nämlich die individuelle Anpassung der Prozedur an Ihre Tabelle, gehen wir dann in der nächsten Woche ein.